Sql Triggers

Question

Sql Triggers

Richard Stevens

SSC Rookie

Points: 34
More actions
February 10, 2007 at 5:59 pm

#92326

I need to automatically update a "LastEditDate" column on a table with the local server time. The only way I could think how to do this was with a trigger. Here's the code:
CREATE
TRIGGER LastEditDate ON ManifestHeader FOR INSERT,UPDATE AS
BEGIN
UPDATE ManifestHeader
SET
DateLastEdit=GetDate()
FROM
ManifestHeader INNER JOIN Inserted ON
ManifestHeader
.ManifestHeaderId = Inserted.ManifestHeaderId
END
Is this the most efficient way to accomplish this in SQL?? You will note that this is a trigger on the ManifestHeader table and when it fires it does an update on the same table the trigger is on.
This concerns me, but how else can I accomplish this??

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

It would be more efficient to set the value of DateLastEdit in the procedure that inserts or updates the data than to use a trigger

Richard Stevens SSC Rookie Points: 34 More actions · Answer 2

The problem is that I want to use the server time and not the client time. Would that be faster even though I would have to query the server for the time before posting the data??

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

There is no reason why you couldn't use GETDATE() in your INSERT or UPDATE statement.

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 4

If you use triggers be sure to add the line

SET NOCOUNT ON after the "AS" to reduce roundtrips.

CREATE TRIGGER LastEditDate ON ManifestHeader FOR INSERT,UPDATE

AS

SET NOCOUNT ON

...

see

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1057991,00.html

Paarthasarathy SSChasing Mays Points: 649 More actions · Answer 5

better way is to define a default and bind to the column as follows.

It is better to avoid triggers to improve the performance.

create default today as getdate()

sp_bindefault today ,'ManifestHeader.DateLastEdit'

Kindest Regards,
Paarthasarathy
Microsoft Certified Technology Specialist
http://paarthasarathyk.blogspot.com

Richard Stevens SSC Rookie Points: 34 More actions · Answer 6

Will that get updated everytime someone edits the record?

Paarthasarathy SSChasing Mays Points: 649 More actions · Answer 7

it will get updated , but will have the detail of only when it got updated latest

Kindest Regards,
Paarthasarathy
Microsoft Certified Technology Specialist
http://paarthasarathyk.blogspot.com

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

No... it only get's updated on INSERT... like any default, it WILL NOT BE APPLIED DURING UPDATES... for example...

USE TEMP

CREATE TABLE ModifyTest(SomeCol INT, ModifiedDate DATETIME)

GO

CREATE DEFAULT TODAY AS GETDATE()

GO

EXEC sp_BinDefault TODAY ,'ModifyTest.ModifiedDate'

GO

INSERT INTO ModifyTest(SomeCol)

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5

SELECT * FROM ModifyTest

GO

WAITFOR DELAY '00:00:05'

UPDATE ModifyTest

SET SomeCol = 10

WHERE SomeCol = 3

SELECT * FROM ModifyTest

DROP TABLE ModifyTest

DROP DEFAULT TODAY

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

Richard,

Michael is correct... the most efficient way is to simply make sure that GETDATE() is used in the procs that update the table. As you point out, that does not account for direct updates via EM or QA and doesn't cover procs that don't update the column.

The trigger you wrote is an AFTER trigger and is very convenient, but as Michaeal pointed out, has quite a bit of overhead. On a million row table, an update of 1400 rows will take 193 milliseconds longer than without a trigger. But if you update a half million rows, it will take 30 seconds longer than without a trigger.

One way around the expense of the AFTER trigger causing, essentially, a double update on all rows updated, is to write an INSTEAD OF TRIGGER... you will find that THAT's a royal pain in the patootie because you will either need a wad of dynamic SQL or COLUMNS_UPDATED to figure out which columns have been updated and only update those columns (along with the edit date column) or you'll need to update ALL columns which *** will*** cause the unnecessary firing of other triggers. Of course, if those other triggers are correctly written to make sure some data changed before taking action, then that'll work.

Either way (Instead Of OR After trigger), you'll have some pretty hefty extra overhead. If you're willing to pay the price, though, the way you did it is one of the easiest ways.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)