Inserting start and end timestamps using triggers on multiple rows

[Emmy2368]

Hi,

I'm a newbie in writing triggers and would appreciate if you could please assist. I want to capture a startdatetime and enddatetime each time PtCt changes value, when this condition is met i.e If(PtCt = 0, Insert timestamp(StartDateTime)) and when the value of PtCt changes from 0 to any other value i.e. PtCt <> 0 to insert timestamp(EndDateTime) . To start a new line again for each PtN when PtCt = 0. Below is my expected output. Please note that PtN can have many occurrences during the day and my intention is to capture the record each time. Thanks in advance for assisting.

PtN PtCt StartDateTime EndDateTime
Pt1 4 02/07/2014 14:02:15 02/07/2014 15:02:15
Pt1 0 02/07/2014 15:02:15 02/07/2014 15:45:50
Pt2 27 02/07/2014 10:05:12 02/07/2014 10:45:11
Pt2 21 02/07/2014 09:13:00 02/07/2014 09:33:09

 

[gk53]

So you want to " capture a startdatetime and enddatetime each time PtCt changes value" and what to to with that? record somewhere? it is unclear What is you table structure? keys?
For " To start a new line again for each PtN when PtCt = 0" why not just set PtCt required field and default value 0?
Is your app will update only PtCt field?

 

[philhege]

If End time is to be assigned only when PtCt changes from 0 to something else, I don't get this row:

Pt1 0 02/07/2014 15:02:15 02/07/2014 15:45:50

-----------
With business clients like mine, you'd be better off herding cats.

 

[Emmy2368]

Hi gk53, Yes column PtCt is the field that should fire the trigger once there's a change. I intend to use getdate() to generate the timestamp which will populate startdatetime when PtCt =0 and another getdate() to populate enddatetime when PtCt value changes from 0 to any other number. I hope my explanation is ok, if not please let me know.
Thanks.

 

[Emmy2368]

Hi Philhege, apologies if my explanation wasn't clear. I have rephrased it, see table below

PtN | PtCt | StartDateTime | EndDateTime
Pt1 | 0 | getdate()* | getdate()**
Pt1 | 0 | getdate()* | getdate()**

When the value of PtCt = 0 then getdate()* and when it changes from 0 to another value e.g. 2 or more, then getdate()**, and then it repeats the same action and writes a new row anytime the condition is met.

Thanks.

 

[gmmastros]

Do you have a column in your table that is the unique identifier for the row?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Emmy2368]

Yes the original table has unique id (Pk) which is PtN. The table only holds one record for each PtN at any point in time. So, I’m trying to build a history table to record all the changes that occur on that table since it only shows the current status. See table layout below
PtN | PtCt |
Pt1 | 2 |
Pt2 | 3 |
Pt3 | 10|
Pt4 |12|
Pt5 |6 |

 

[gmmastros]

It seems to me like this is a 3 step process.

1. Set the EndDateTime of "older" rows when PtCt <> 0
2. Insert a new row in history whet PtCt = 0
3. Insert a new row when the item is not found in history.

This is what I've come up with based on my understanding of your requirements.

Create Table Original(PtN VarChar(20), PtCt Int)
GO
Create Table History(PtN VarChar(20), PtCt Int, StartDateTime DateTime, EndDateTime Datetime)
GO
Create Trigger CaptureHistory On Original For Insert, Update
AS
SET NOCOUNT ON;

With HistoryData As
(
  Select  PtN, 
          StartDateTime, 
          EndDateTime,
          Row_Number() Over(Partition By PtN Order By StartDateTime DESC) As RowId 
  From    History
)
Update  HistoryData
Set     HistoryData.EndDateTime = GetDate()
From    HistoryData
        Inner Join Inserted I
          On HistoryData.PtN = I.PtN
          And HistoryData.RowId = 1
Where   I.PtCt <> 0;

Insert
Into    History(PtN, PtCt, StartDateTime, EndDateTime)
Select  I.PtN, I.PtCt, GetDate(), GetDate()
From    Inserted I
Where   I.PtCt = 0;

-- Insert rows in to history where
-- they don't already exist.  This should
-- only happen if the insert has a value other
-- than 0 because this would have been handled
-- by the previous insert.

Insert
Into    History(PtN, PtCt, StartDateTime, EndDateTime)
Select  I.PtN, I.PtCt, GetDate(), GetDate()
From    Inserted I
        Left Join History
          On I.PtN = History.PtN
Where   History.PtN Is NULL;

This is a somewhat complicated procedure. You should test this very well.

This is what I used to test it, but it is far from thorough.

Delete From Original
Delete From History

Insert Into Original(PtN, PtCt) Values('Pt1', '7')
WaitFor Delay '00:00:01'

Update Original Set PtCt = 10 Where PtN = 'Pt1'
WaitFor Delay '00:00:01'

Update Original Set PtCt = 12 Where PtN = 'Pt1'
WaitFor Delay '00:00:01'

Update Original Set PtCt = 0 Where PtN = 'Pt1'
WaitFor Delay '00:00:01'

Update Original Set PtCt = 3 Where PtN = 'Pt1'
WaitFor Delay '00:00:01'

Update Original Set PtCt = 7 Where PtN = 'Pt1'
WaitFor Delay '00:00:01'

Update Original Set PtCt = 11 Where PtN = 'Pt1'
WaitFor Delay '00:00:01'

Update Original Set PtCt = 0 Where PtN = 'Pt1'
WaitFor Delay '00:00:01'

Update Original Set PtCt = 4 Where PtN = 'Pt1'

Select * From History

BTW, it wouldn't take much effort to capture the last value of PtCt so that you would have a starting value and ending value.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Emmy2368]

Hi gmmastros, thank you so much. The startdatetime is working perfectly well as I expected and it writes a new record each time the value of PtCt =0, but the EndDateTime isn't yet. Instead what it's currently doing is defaulting all the records with same enddatetime as at the time the value changes. Below is the output generated

PtN| PtCt| StartDateTime |EndDateTime
Pt1| 0| 2014-07-18 20:56:27.087 |2014-07-18 21:15:07.920
Pt1| 0| 2014-07-18 20:58:50.353 |2014-07-18 21:15:07.920
Pt1| 0| 2014-07-18 21:03:28.393 |2014-07-18 21:15:07.920
Pt1| 0| 2014-07-18 21:08:25.677 |2014-07-18 21:15:07.920
Pt2| 0| 2014-07-18 21:08:28.777 |2014-07-18 21:15:07.920
Pt2| 0| 2014-07-18 21:03:32.107 |2014-07-18 21:15:07.920

I would want it to hold different values of EndDatetime as at the time the event changed from 0 to any other value rather than all enddatetime being same.

Thanks for taking time to assist me.

 

[gmmastros]

Have you tried the code I provided. I was certain that it worked correctly? Is it possible that you missed something in the process of converting the code over to use your tables?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Emmy2368]

Thanks gmmastros for your great assistance. I tried different approach as below but with the initial guidance from you and it's working very well.
Insert
Into History(PtN, PtCt, StartDateTime, EndDateTime)
Select I.PtN, I.PtCt, GetDate(), NULL
From Inserted I
INNER JOIN Deleted d
on d.PtN = i.PtN
Where I.PtCt = 0
and d.PtCt <> 0;


Update History
SET EndDateTime = getdate()
from History h
inner join inserted I
on I.PtN = h.PtN
inner join deleted d
on d.PtN = I.PtN
where i.PtCt <> 0
AND d.PtCt = 0
and EndDateTime is null