Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    2

    Unanswered: Need an example of a trigger.

    Hello,

    I'm a newbie to the MS SQL db engine, and I'd like to have some help with Transact-SQL. I'd like to know the code to create a trigger to do the following. I have two tables, one references the other. I'd like the database to automatically delete the records from table2 when the record that they reference in table1 is deleted. Much thanks.

  2. #2
    Join Date
    Jan 2003
    Posts
    6
    Try this



    create trigger dbo.trigger_deleterecs on table1
    for delete
    as
    begin

    declare @refcolumn datatypehere
    select @refcolumn = deleted.refcolumn from deleted
    delete from table2 where table2refcolumn = @refcolumn

    end

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    how about:

    Code:
    create trigger dbo.trigger_deleterecs on table1
    for delete
    as
    begin
       delete from table2 where refcolumn in (select refcolumn from deleted)
    end
    or

    Code:
    create trigger dbo.trigger_deleterecs on table1
    for delete
    as
    begin
       delete t2
         from table2 t2 
         join deleted d on t2.refcolumn = d.refcolumn
    end
    by using either of these you will delete all records when multipule records are deleted.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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