Post reply

Update triggers

  • November 14, 2008 at 4:49 am

    #215178

    At the moment our client system update record Created_Date and Amended_date fields from the

    users system datetime, which is causing problems due differences in different time zones. To try and resolve this problem I have been given the task to look at the options/feasibility of implementing triggers on the database.

    insert trigger no problem.

    create trigger new_record on customer

    after insert as

    update customer set create_date = getdate()

    where id = (select max(id) from customer)

    The update trigger is proving more troublesome!

    create trigger update_record on customer

    after update as

    update customer set amend_date = getdate()

    where id = ??

    Am I missing one of those "bleedingly obvious" system variables that will give me the current record?

  • November 14, 2008 at 4:54 am

    #898484

    please look up the deleted and inserted logical tables used by DML triggers.

  • November 14, 2008 at 6:01 am

    #898506

    --[Inserted] And [Deleted] tables are special virtual tables that contain all the records affected by the insert or delete; it is critical that in a trigger you use a set based operation to update ALL records that could exist in these virtual tables, because a trigger runs for a single operation, not per row.

    --note an update has records in both [Inserted] and [Deleted]...one value is removed, and another value inserted in it's place.

    CREATE TRIGGER new_record ON customer

    AFTER INSERT AS

    UPDATE customer

    SET create_date = getdate()

    FROM INSERTED

    WHERE customer.id = INSERTED.id

    CREATE TRIGGER update_record ON customer

    AFTER UPDATE AS

    UPDATE customer

    SET amend_date = getdate()

    FROM INSERTED

    WHERE customer.id = INSERTED.id

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 14, 2008 at 6:17 am

    #898515

    Thanks for that.

  • November 14, 2008 at 9:10 am

    #898692

    One thng to bear in mind. In SQL Server triggers are not fired for each row that's inserted/updated. They fire once for an insert/update/delete operation and the inserted and/or deleted tables contain all of the qualifying rows.

    Don't write a trigger assuming there's only one row in the inserted or deleted tables

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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