Post reply

Create Trigger

  • July 9, 2002 at 12:42 pm

    #79055

    Hi,

    I have two tables(table1 and table2).

    Table1 has a unique number generator as it's primary key(IDNum).

    Table also has a field called IDNum which is a numeric field that is the same as the one in Table1.

    I have never created triggers before so I need help creating a trigger - whenever a IDNum is delete from table1, I want all the IDNums from table2 that match the IDNum in table1 being deleted to also be deleted from table2.

    If someone could help me code this trigger, I will really appreciate it.

    Thankx

  • July 9, 2002 at 12:45 pm

    #432276

    Try this:

    create trigger d_whatevertable on whatevertable for delete as

    set nocount on

    delete from tableb where idnum in (select idnum from deleted)

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

    Andy
    Connect with me on LinkedIn

  • July 9, 2002 at 2:22 pm

    #432286

    Thanks Andy,

    But will this work even though the two fields are different ie one is a unique number generator and the other is not(it is numberic).

    Also pardon my ignorance, but how does one execute triggers.

  • July 9, 2002 at 2:34 pm

    #432287

    You just type the text of the trigger in Query Analyzer and then run it, after that the trigger fires whenever modifications of the selected type occur. Doesnt matter that the field is auto generated as long as they are the same type (or can be converted) and accurately reflect the relationship.

    Note that in SQL2K you can do this more cleanly by using cascading deletes.

    I recommend you spend a few minutes reading the help file on triggers before proceeding.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

    Andy
    Connect with me on LinkedIn

  • July 10, 2002 at 7:29 am

    #432332

    Thanks Andy for the clarification.

    I will definetely read the help file before doing anything.

    Just another quick question,

    Do the tables have to be linked for a triger to succeed ie does there have to be a relationship between the two tables. I haven't linked the two tables.

    Thanks.

  • July 10, 2002 at 7:45 am

    #432335

    No, the tables don't have to be linked.

    The easiest way to think of triggers is as a specialized stored procedure. It is called automatically by SQL Server when the operation it is configured for (INSERT, UPDATE, and/or DELETE) occurs on a table the trigger is defined against.

    Imagine if you were manually able to control all access to a given table. You had a set of instructions that said, "Run this stored procedure whenever anyone executes an INSERT against this table." That's pretty much how a trigger works.

    K. Brian Kelley

    bkelley@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

Viewing 6 posts - 1 through 6 (of 6 total)

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