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?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.|||create trigger blah on update
as
IF UPDATE(contacttype)
BEGIN
IF SELECT contacttype FROM inserted = 2
BEGIN
...insert code here...
END
END|||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|||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.|||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.|||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|||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|||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