Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2004
    Posts
    4

    Unanswered: Conditional SQL Triggers

    Hi, I've been handed a task at work where I need to use SQL triggers to solve the problem.

    I need to be able to run a Trigger when, and only when, a cell in a specific column in a row changes to a specific value.

    For instance, say I have the following table:
    CREATE TABLE source
    (
    ID tinyint NOT NULL,
    contacttype tinyint NOT NULL,
    )

    If one of the rows is UPDATE'd to contacttype = 2, I want to fire a trigger, but not if it changes to anything else.

    How can this be performed?

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    You can attach a trigger to a table to respond to inserts, deletes or updates. I think you can narrow it down to a specific column (if columns_updated). Anything else goes in the trigger itself. See BOL for TRIGGER.

  3. #3
    Join Date
    Sep 2004
    Posts
    22
    create trigger blah on update
    as
    IF UPDATE(contacttype)
    BEGIN
    IF SELECT contacttype FROM inserted = 2
    BEGIN
    ...insert code here...
    END
    END

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Understand, the TRIGGER will always fire. As shown, you want to control the logic inside the trigger.

    What action do you need to take?

    Just make sure the affected rows use the id of that row as the reason to modify that data
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Oct 2004
    Posts
    4
    Thanks for all the replies!

    I tried mitchell007's code, and it helped a lot. I am now able to run SQL statements if a certain column is updated.
    But I had a problem with the line: IF SELECT contacttype FROM inserted = 2
    This results in a parse error. "Incorrect syntax near the keyword SELECT" and "Incorrect syntax near '='."

    Here is my trigger code:
    CREATE TRIGGER AddContact ON ContactTable
    FOR UPDATE
    AS
    IF UPDATE(contacttype)
    BEGIN
    IF SELECT contacttype from inserted = 2
    BEGIN
    print 'contacttype modified!'
    END
    END

    Brett, my ultimate goal is to detect when a row in a contact table changes the value of the 'contacttype' column. If a row is created with contacttype = 2, or if an existing row is updated to that value, I want to create a new row in a different database.

    Also, how can I know which row was altered? When all the trigger filters pass, I want to extract the updated row, and insert most of it's data into a different database.

  6. #6
    Join Date
    Oct 2004
    Posts
    4
    Okay, I've been working with this for some hours now, and have come a little further, but still have some obstacles to climb over.

    First, when I try to use inserted.contacttype to get the value from the updated table, and into my new table, I get a "Error 128: The name 'contacttype' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."

    This works:
    BEGIN
    INSERT INTO tmp_mycoteam.dbo.Firma VALUES (1,2,3,4)
    END

    But this doesn't:
    BEGIN
    INSERT INTO tmp_mycoteam.dbo.Firma VALUES (inserted.contacttype,2,3,4)
    END

    Second, I am still struggeling with getting the trigger to run the INSERT statement only when contacttype changes to a specific number. Right now the INSERT fires when the contacttype field is updated to any value.

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    The select statement after the update is not necessary. Try this:


    CREATE TRIGGER AddContact ON ContactTable
    FOR UPDATE
    AS
    IF UPDATE(contacttype)
    BEGIN
    IF inserted.contacttype = 2
    BEGIN
    <perform needed operations here>
    END
    END

  8. #8
    Join Date
    Oct 2004
    Posts
    4
    Here is the complete trigger I've written so far, with tomh53's suggestion. With the "IF inserted.category_idx = 2" line I get a "Error 107: The column prefix 'inserted' does not match with a table name or alias name used in the query."

    CREATE TRIGGER conditionalinsert
    ON crm5.contact
    FOR UPDATE
    AS
    IF UPDATE(category_idx)
    BEGIN
    IF inserted.category_idx = 2
    BEGIN
    INSERT INTO tmp_mycoteam.dbo.Firma SELECT department, contact_id, name, number1, number2, business_idx, orgNr from inserted
    END
    END

  9. #9
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Scalpel ... my apologies for ** bad ** code. Try this:

    CREATE TRIGGER conditionalinsert
    ON crm5.contact
    FOR UPDATE
    AS
    IF UPDATE(category_idx)
    BEGIN
    INSERT INTO tmp_mycoteam.dbo.Firma
    SELECT department, contact_id, name, number1, number2, business_idx, orgNr
    FROM inserted
    WHERE inserted.category_idx = 2
    END

Posting Permissions

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