Results 1 to 6 of 6

Thread: Update Trigger

  1. #1
    Join Date
    Aug 2007
    Posts
    29

    Unanswered: Update Trigger

    This is something I have limited experience on. I need to create an update trigger after insert on one table that updates a completely different production table. My development server is being re-done and I'm not sure when it will be back on line. I have created some audit tables using triggers but they a simple inserts into a new table.

    I believe the trigger statement should look like this, any advice would be appreciated.

    Thanks in advance.

    CREATE TRIGGER OB$1InsertAudit ON OB$0001
    AFTER INSERT
    AS
    Begin
    Update AX$0001
    Set AX$0001.receiveddate=getdate(), AX$0001.docreviewstatus=null where Ob$0001.objid=AX$0001.refobjid and OB$0001.recordtype=6 and
    OB$0001.descriptor in('Ancillary Services Tracking Form',
    'FCE Referral Request',
    'Remain At Work',
    'Voc Rehab Job Retention')

    End

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Code:
    CREATE TRIGGER OB$1InsertAudit ON OB$0001
    AFTER UPDATE ---?
    AS
    
    Update A Set receiveddate=getdate(), docreviewstatus=null 
    FROM AX$0001 A
    JOIN inserted
    ON inserted.objid = A.refobjid
    where inserted.recordtype=6 
    and inserted.descriptor in('Ancillary Services Tracking Form',
    'FCE Referral Request',
    'Remain At Work',
    'Voc Rehab Job Retention')
    
    GO
    Last edited by Thrasymachus; 04-22-08 at 11:45.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Aug 2007
    Posts
    29
    Thank you, I guess I wasn't as close as I thought.

  4. #4
    Join Date
    Aug 2007
    Posts
    29
    Wouldn't it be after insert since this would be one time shot and there really shouldn't be updates on this table after insert unless there's an error by a user that has to be corrected?

    CREATE TRIGGER OB$1InsertAXupdate ON OB$0001
    AFTER INSERT
    AS

    Update A Set receiveddate=getdate(), docreviewstatus=null
    FROM AX$0001 A
    JOIN inserted
    ON inserted.objid = A.refobjid
    where inserted.recordtype=6
    and inserted.descriptor in('Ancillary Services Tracking Form',
    'FCE Referral Request',
    'Remain At Work',
    'Voc Rehab Job Retention')

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    are we certain that records already exist in [AX$0001] for our objrefid. Then yes, change my code back to AFTER INSERT
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Aug 2007
    Posts
    29
    This is were I'm a little lost on the Triggers. I pulled some data and it appears that the AX$0001 is created a few mili seconds ahead of the OB$0001 record according to the audit tables.

Posting Permissions

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