Combining INSERT And UPDATE Triggers?

Question

Post reply

Combining INSERT And UPDATE Triggers?

dajonx

SSCertifiable

Points: 6950
More actions
June 2, 2010 at 1:19 pm

#235884

Hi,
I have a table:
CREATE TABLE Blah (
Pid INT,
CreatedUserID VARCHAR(50),
CreatedDate DATETIME,
ModifiedDate DATETIME)
I have one trigger that updates the CreatedUserID and CreatedDate when data is inserted:
CREATE TRIGGER T_Blah_Insert
ON Blah
AFTER INSERT
AS
BEGIN
UPDATE a
SET CreatedUserID = 'Me',
CreatedDate = GETDATE()
FROM Blah a
JOIN INSERTED b
ON a.Pid = b.Pid
END
GO
I also have another trigger that updates the ModifiedDate when a record is being updated:
CREATE TRIGGER T_Blah_Updated
ON Blah
AFTER UPDATE
AS
BEGIN
UPDATE a
SET ModifiedDate = GETDATE()
FROM Blah a
JOIN INSERTED b
ON a.Pid = b.Pid
END
GO
I was told that these two triggers can be combined, but I don't see how they can be. Could anyone please tell me if it's even possible? If so, how?
Thanks!
My Blog!

Viewing 4 posts - 1 through 3 (of 3 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Regarding the INSERT trigger: I would use DEFAULT values for those two columns instead.

Regarding UPADTE: why don't you include the ModifiedDate column in your update procedure / statement?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

dajonx SSCertifiable Points: 6950 More actions · Answer 2

lmu92 (6/2/2010)
Regarding the INSERT trigger: I would use DEFAULT values for those two columns instead.
Regarding UPADTE: why don't you include the ModifiedDate column in your update procedure / statement?

Thank you for responding.

DEFAULT is a good idea! I forgot all about those... :ermm:

Can you please explain more about your question regarding the UPDATE? I don't quite understand what you mean. Sorry... :unsure:

My Blog!

LutzM SSC Guru Points: 107049 More actions · Answer 3

I don't know how you update that table (through stored procedure or a direct statement)...

Let's assume the following code:

UPDATE Blah

SET col1=1, col2=...

FROM Blah

WHERE condition

Why not change it to

UPDATE Blah

SET col1=1, col2=..., ModifiedDate = GETDATE()

FROM Blah

WHERE condition

This would be a lot easier to manage if you'd use a concept of procedure based data manipulation instead of direct updates against the table... So I'm not sure if really is an option.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]