September 4, 2009 at 11:50 am
Howdy folks,
I just realized I'm disappointed with sql server 2008's trigger capabilities because I was not quite able to implement one of the developer's request.
I need to create a trigger that updates a DateModifed column for a given set of rows that are being updated by a DML statement. After some internet research, I came up with something like this:
CREATE TRIGGER trg_UpdateDateModified
ON Employees
INSTEAD OF UPDATE
AS
BEGIN
IF UPDATE(FirstName)
UPDATE
E
SET
E.DateModified = GETDATE(),
E.FirstName = I.FirstName
FROM
Employees E, inserted I
WHERE
E.FirstName=I.FirstName
END
GO
I see some huge problems with this:
- The trigger will fire only when FirstName column will be targeted with an update. If I want the trigger to fire for any columne that will be targeted with an update, I'll have to implement conditions within the trigger for every single column part of the table - that's kind of unacceptable;
- Conversely, if I create a generic trigger that fires for any update, without to specify the column in IF UPDATE(column) I will not be able to update the DateModified rows that were targeted by the triggering DML statement in the first place. The result would be that all the DateModified entries in the whole table would be updated;
- Assuming I choose to go down on the path of creating the trigger condition for every single column in the table, the update statement inside trigger is totally inefficient because it will have to use indexes to find out which rows have been updated (it's an update join as you can see) so for no reason on earth I will create 1 index for each column in the table.
- Even so, the trigger will yield improper results when the inside update-join will affect existing rows in the table that WERE not targeted by triggering DML statement. I've seen it with my own eyes in the ensuing tests.
- Assuming that I'm still nuts enough to implement the above solution, I'll have to write triggers like this for every single freaking table and column in the db; the db is supposed to replicate in large lines whatever is in a old mysql db, which as y'all know has one of the few nifty things found in mysql:
`date_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
To wrap up things, in Oracle the above state trigger would look absolutely simple and elegant, like this:
CREATE OR REPLACE TRIGGER trg_UpdateDateModified
BEFORE INSERT OR UPDATE
ON Employees
FOR EACH ROW
DECLARE
BEGIN
IF INSERTING THEN
:NEW.DateCreated := SYSDATE;
:NEW.DateModified := SYSDATE;
END IF;
IF UPDATING THEN
:NEW.DateModified := SYSDATE;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20101, 'Error : '||SQLCODE||' - '||SQLERRM);
END;
So, I dare you to solve this one.
September 4, 2009 at 12:04 pm
Try this:
CREATE TRIGGER trg_UpdateDateModified
ON Employees
FOR UPDATE
AS
UPDATE Employees
SET DateModified = getdate()
FROM Employees
INNER JOIN inserted
on Employees.ID = inserted.ID;
I'm assuming the existence of an ID column in the Employees table. You'll need to modify it to use the correct primary key in the join if ID isn't what you're using.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 12:11 pm
I'll try it in a sec, but I couldn't help it to ask, where's that inserted.ID coming from? Yes, I do have a PK in the table, but if the triggering DML statement doesn't involve any PK, how's that going to help?
September 4, 2009 at 12:16 pm
Doesn't matter if the DML has the ID column in it or not. Triggers have two "tables", one called "inserted", one called "deleted". The inserted one is a snapshot of each row that was either inserted or updated. The deleted one is a snapshot of each row that was deleted, or each row that was updated, with the pre-update data. In other words, inserted = after, deleted = before, for updates.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 12:35 pm
Ok, that I must be doing something wrong, because remember getting through this variant of the trigger and it doesn't do what it supposed to do.
Here is the table:
CREATE TABLE [dbo].[Employees](
[DateCreated] [datetime] NULL,
[DateModified] [datetime] NULL,
[Id] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](100) NULL,
[LastName] [nvarchar](100) NULL,
[DeptIdt] [int] NOT NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employees] WITH CHECK ADD CONSTRAINT [FK_Employees_Departments] FOREIGN KEY([DeptIdt])
REFERENCES [dbo].[Departments] ([Id])
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Departments]
GO
The entire data set is really simple, for exemplification purposes:
DateCreated DateModified IdFirstNameLastNameDeptIdt
2009-09-04 11:23:48.4602009-09-04 11:23:48.4608JohnDoe1
2009-09-04 11:23:48.4602009-09-04 11:23:48.4609MarryDoe2
2009-09-04 11:23:48.4602009-09-04 11:23:48.46010JohnLong Silver3
2009-09-04 11:23:48.4602009-09-04 11:23:48.46011TedKing2
The trigger is in place and looking like this:
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trg_UpdateDateModified]'))
DROP TRIGGER [dbo].[trg_UpdateDateModified]
GO
CREATE TRIGGER [dbo].[trg_UpdateDateModified]
ON [dbo].[Employees]
INSTEAD OF UPDATE
AS
BEGIN
UPDATE
Employees
SET
DateModified = getdate()
FROM
Employees
INNER JOIN inserted
ON Employees.ID = inserted.ID;
END
GO
I run the following queries:
select * from Employees
update Employees SET FirstName= 'Johnny' where Id=8
select * From Employees
and finally, here are the results:
BEFORE update
2009-09-04 11:23:48.4602009-09-04 11:23:48.4608JohnDoe1
2009-09-04 11:23:48.4602009-09-04 11:23:48.4609MarryDoe2
2009-09-04 11:23:48.4602009-09-04 11:23:48.46010JohnLong Silver3
2009-09-04 11:23:48.4602009-09-04 11:23:48.46011TedKing2
AFTER update:
2009-09-04 11:23:48.4602009-09-04 11:28:28.8138JohnDoe1
2009-09-04 11:23:48.4602009-09-04 11:23:48.4609MarryDoe2
2009-09-04 11:23:48.4602009-09-04 11:23:48.46010JohnLong Silver3
2009-09-04 11:23:48.4602009-09-04 11:23:48.46011TedKing2
As you can see, the DateModified is updated, but NOT the FirstName column. Am I missing something here?
September 4, 2009 at 12:38 pm
That's why my version of the trigger is "For Update", not "Instead of Update". That's one of the key differences.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 12:39 pm
Nevermind my previous question, I just realized that I'm using INSTEAD OF when you were using FOR UPDATE.
I corrected the trigger definition, tested it and it seems to work.
Any reasons NOT to have such a trigger on production?
September 4, 2009 at 12:54 pm
Also a question about this trigger. Is it acting like an AFTER trigger, generating an additional IO for each DML or is it doing the changes in the buffer and applying them after it finished execution?
If it's an after trigger, I'm not gonna recommend to the developer due to the fact it might create problems in the long run - additional IO requests just for a simple update on DateModified.
September 4, 2009 at 12:54 pm
The main objection to any trigger is that it's hidden from the developers. A more standard way to update that column would be to make sure the update proc includes it in the definition, and then make sure no updates are done to the table except through that proc. Then the code isn't hidden from anyone. Of course, it's only hidden from devs who don't really know their business, but that's an unfortunately high percentage.
The other reason to not use such a trigger is that the whole "DateUpdated" column is, in most cases, close to useless.
If it's being used to determine whether the data should be archived or not, then it's useful. If it's being used as part of an audit trail, it's useless. (Just like the common "UpdatedBy" column is useless.)
Other than those two things, no, such triggers are pretty common, and can be considered a standard practice.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 12:59 pm
Daniel C (9/4/2009)
Also a question about this trigger. Is it acting like an AFTER trigger, generating an additional IO for each DML or is it doing the changes in the buffer and applying them after it finished execution?If it's an after trigger, I'm not gonna recommend to the developer due to the fact it might create problems in the long run - additional IO requests just for a simple update on DateModified.
Triggers get incorporated into the transaction that causes them to be fired off. Test it, of course, but it shouldn't cause any significant increase in I/O over the original transaction.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 1:04 pm
You really answered all my questions and shed some light.
Thanks a bunch for the efforts 🙂
September 4, 2009 at 1:25 pm
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply