Trigger question

  • 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.

  • 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

  • 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?

  • 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

  • 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?

  • 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

  • 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?

  • 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.

  • 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

  • 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

  • You really answered all my questions and shed some light.

    Thanks a bunch for the efforts 🙂

  • 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