Trigger updates entrie table 2

[forecasting]

When a time stamped record is inserted from a separate application, a trigger is executed that expands the time stamp into several fields. However, I discovered that the trigger code is updating every record in the table, not just the one being inserted. Any ideas about how to stop this?

CREATE TRIGGER [FILLDL2234A] ON dbo.DL2234A
FOR INSERT,UPDATE
AS
BEGIN
UPDATE DL2234A
SET
FYEAR = DATEPART(YYYY,STAMP-(.5/1440)),
DAYOFWEEK = DATEPART(DW,STAMP-(.5/1440)),
FDAY = DATEPART(DD,STAMP-(.5/1440)),
FMONTH = DATEPART(MM,STAMP-(.5/1440)),
FWEEK = DATEPART(WK,STAMP-(.5/1440)),
FHOUR = DATEPART(HH,STAMP-(.5/1440)),
FDATE = CONVERT(CHAR,STAMP-(.5/1440),101),
FTIME = CONVERT(CHAR,STAMP,108)
END

 

[ecobb]

You need to specify a WHERE clause in the trigger. Like this: WHERE PrimaryKeyName = @@identity

The '@@identity' value tells the trigger to update only the row that was just inserted/updated.

Hope this helps!

 

[forecasting]

I inherited this database design. There is no identity field, the primary key is the timestamp. Is there anything else I can do? or will @@identity refer to the timestamp?

 

[fluteplr]

No @@identity only refers the the last identity field updated.

If I understand you correctly the stamp field is the primary key.

You need to use the inserted psuedo table which exists in triggers and contains the data that was just inserted.


CREATE TRIGGER [FILLDL2234A] ON dbo.DL2234A
FOR INSERT,UPDATE
AS
BEGIN
UPDATE DL2234A
SET
FYEAR = DATEPART(YYYY,STAMP-(.5/1440)),
DAYOFWEEK = DATEPART(DW,STAMP-(.5/1440)),
FDAY = DATEPART(DD,STAMP-(.5/1440)),
FMONTH = DATEPART(MM,STAMP-(.5/1440)),
FWEEK = DATEPART(WK,STAMP-(.5/1440)),
FHOUR = DATEPART(HH,STAMP-(.5/1440)),
FDATE = CONVERT(CHAR,STAMP-(.5/1440),101),
FTIME = CONVERT(CHAR,STAMP,108)
from inserted
where inserted.stamp = dl2234a.stamp
END

Also I question the need for this trigger in the first place, since all of the data can obviously be derived from the stamp field, why not just create it when you query the data?

 

[forecasting]

Fluteplr - thanks for the fix!

I believe the reason the original designer inserted these fields in the database was to speed retreival from certain queries which are constructed in the client application. I agree with your questioning the need. Other people in the company must have questioned his designs as well because he is no longer with the firm.

Right now we are performing triage on the application and database. I am really the domain expert with enough knowlege about code to pitch in and off load some of the programming/debug work. Thanks for making me look smarter than I am.

 

[fluteplr]

No problem. Just remember a working definition of expert is:

They guy who has read one more chapter in the book than every one else.