Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2003
    Location
    East Grinstead ,UK
    Posts
    19

    Unanswered: Help with Trigger

    Hi There, I'm trying to create an insert trigger in SQL Server 2000 on onw of my table. The idea is that after inserting a value in a column i would like to insert that same value in another column in a different table. So far I can do a trigger whereby a value gets inserted into a different column but in the same table.

    My table structure is:

    qms_proc:

    proc_id int pk
    proc_title varchar
    proc_body


    link_hold:

    proc_id fk

    I want the value of proc_id in qms_proc to be inserted into proc_id in the link_hold table.

    Unfortunately I've been trying for about two days and no luck. My sql statement for the trigger is as follows:

    ALTER TRIGGER [TUpIt] ON [qms_proc]
    FOR INSERT,UPDATE AS
    DECLARE @id int

    IF UPDATE (proc_id)
    begin

    UPDATE link_hold
    set proc_id = i.proc_id
    from qms_proc

    PLEASE HELP!!!!!
    What am I doing wrong?

    Thanks
    am328

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Do you want to insert or update the value?
    If it is a PK then it should never be updated.
    Use inserted and updated tables to prevent accessing all records

    see
    www.nigelrivett.com
    Triggers

    You will need to use the PK on the table to match up the records from inserted and deleted so what you are trying to do may not be possible. The design should be corrected anyway.


    IF UPDATE (proc_id)
    UPDATE link_hold
    set proc_id = i.proc_id
    from inserted, deleted
    where link_hold.proc_id = deleted.proc_id
    and inserted.PK = deleted.PK
    and inserted.proc_id <> deleted.proc_id

  3. #3
    Join Date
    Mar 2003
    Location
    East Grinstead ,UK
    Posts
    19
    Hi,
    I want to insert the value of the primary key into the foreign key in the link_hold table. The idea is :
    If I create a qms_proc( the proc_id is primary key in this table,the primary key is an int that I generate), I would like the proc_id to be inserted into the link_hold table, where the proc_id is a FK.

    Originally posted by nigelrivett
    Do you want to insert or update the value?
    If it is a PK then it should never be updated.
    Use inserted and updated tables to prevent accessing all records

    see
    www.nigelrivett.com
    Triggers

    You will need to use the PK on the table to match up the records from inserted and deleted so what you are trying to do may not be possible. The design should be corrected anyway.


    IF UPDATE (proc_id)
    UPDATE link_hold
    set proc_id = i.proc_id
    from inserted, deleted
    where link_hold.proc_id = deleted.proc_id
    and inserted.PK = deleted.PK
    and inserted.proc_id <> deleted.proc_id

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    You want to insert new proc_id's into link_hold

    In that case this just needs to be a for insert trigger and insert from inserted.

    ALTER TRIGGER [TUpIt] ON [qms_proc]
    FOR INSERT AS

    insert link_hold
    select proc_id
    from inserted

  5. #5
    Join Date
    Mar 2003
    Location
    East Grinstead ,UK
    Posts
    19
    Hi Again,
    Just tested your example, but I'm getting the following error:
    'Insert Error: Column name or number of supplied values does not match table definition.'
    Any ideas?



    Originally posted by nigelrivett
    You want to insert new proc_id's into link_hold

    In that case this just needs to be a for insert trigger and insert from inserted.

    ALTER TRIGGER [TUpIt] ON [qms_proc]
    FOR INSERT AS

    insert link_hold
    select proc_id
    from inserted

  6. #6
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    insert link_hold
    select proc_id
    from inserted

    the insert statement needs to match the table def which you haven't posted.
    You should name the column list.

  7. #7
    Join Date
    Mar 2003
    Location
    East Grinstead ,UK
    Posts
    19

    Help Triggers

    Got it working .... thanks a million

  8. #8
    Join Date
    Mar 2003
    Location
    East Grinstead ,UK
    Posts
    19

    Re: Help Triggers

    Back again!!
    If i want to delete the pk can i create another trigger to delete the fk?
    If so, how do i do that?

    Thanks...again

  9. #9
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    You can't use an after trigger as the delete will already have failed by the time the trigger fires.
    You can do it with an instead of trigger though. Just do the fk delete before the pk delete which you will have to repeat in the trigger.

    creeate trigger tr_del on tbl1 instead of delete
    as

    delete tbl2 from deleted where tbl2.fk = deleted.pk
    if @@error<>0
    rollback tran
    delete tbl1.pk from deleted where tbl1.pk = deleted.pk
    if @@error<>0
    rollback tran

  10. #10
    Join Date
    Mar 2003
    Location
    East Grinstead ,UK
    Posts
    19
    Hi Again,
    The trigger seems to delete the fk but not the pk. I'm getting the following error:
    Server: Msg 208, Level 16, State 1, Procedure tr_del, Line 7
    Invalid object name 'qms_link.link_id'.
    This is my statement


    ALTER trigger tr_del on qms_link instead of delete
    as

    delete link_hold from deleted where link_hold.link_id = deleted.link_id
    if @@error<>0
    rollback tran
    delete qms_link.link_id from deleted where qms_link.link_id = deleted.link_id
    if @@error<>0
    rollback tran

    Any ideas?

    am328


    Originally posted by nigelrivett
    You can't use an after trigger as the delete will already have failed by the time the trigger fires.
    You can do it with an instead of trigger though. Just do the fk delete before the pk delete which you will have to repeat in the trigger.

    creeate trigger tr_del on tbl1 instead of delete
    as

    delete tbl2 from deleted where tbl2.fk = deleted.pk
    if @@error<>0
    rollback tran
    delete tbl1.pk from deleted where tbl1.pk = deleted.pk
    if @@error<>0
    rollback tran

Posting Permissions

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