Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    42

    Exclamation Unanswered: Trigger to trace updates

    Hello,

    I'm trying to create a trigger to trap any insert, delete or update on the triggered table. I have an audit table in which I insert the info about the updated record. After that I update a small datawarehouse based on that audit table.

    Here is my code for my trigger :
    CREATE TRIGGER trgFGCHTR ON fgchtr
    FOR INSERT, UPDATE, DELETE
    AS
    DECLARE @TypeTrans CHAR(6)
    IF EXISTS(SELECT * FROM inserted) AND IF EXISTS(SELECT * FROM deleted)
    SET @TypeTrans = 'UPDATE'
    ELSE IF EXISTS(SELECT * FROM inserted)
    SET @TypeTrans = 'INSERT'
    ELSE IF EXISTS(SELECT * FROM deleted)
    SET @TypeTrans = 'DELETE'

    IF @TypeTrans = 'DELETE'
    BEGIN
    INSERT INTO entrepot_audit(trx_type, trx_timestamp, table_name, pkey_virtuo)
    SELECT 'D', getdate(), 'FGCHTR', ak_aux + char(27) + cast(sq as varchar(6))
    FROM deleted
    END

    IF @TypeTrans = 'INSERT'
    BEGIN
    INSERT INTO entrepot_audit(trx_type, trx_timestamp, table_name, pkey_virtuo)
    SELECT 'I', getdate(), 'FGCHTR', ak_aux + char(27) + cast(sq as varchar(6))
    FROM inserted
    END

    IF @TypeTrans = 'UPDATE'
    BEGIN
    INSERT INTO entrepot_audit(trx_type, trx_timestamp, table_name, pkey_virtuo)
    SELECT 'I', getdate(), 'FGCHTR', ak_aux + char(27) + cast(sq as varchar(6))
    FROM updated
    END

    Something is wrong, I don't know what.

    Thanks a million !

    JFS.

  2. #2
    Join Date
    Aug 2002
    Posts
    29

    Re: Trigger to trace updates

    I just took you code and threw it into Query Analyzer. It didn't parse.
    Remove the second "IF".


    Your code:
    ...
    IF EXISTS(SELECT * FROM inserted) AND IF EXISTS(SELECT * FROM deleted)
    ...


    Correct code:
    ...
    IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
    ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •