March 19, 2002 at 12:15 pm
In oracle, we would create a trigger based on the change on a column. Could SQL Server do it? If yes, what is the syntax?
March 19, 2002 at 1:14 pm
You want to use IF UPDATE ( column ), see CREATE TRIGGER in SQL BOL.
IF UPDATE (column)
Tests for an INSERT or UPDATE action to a specified column and is not used with DELETE operations. More than one column can be specified. Because the table name is specified in the ON clause, do not include the table name before the column name in an IF UPDATE clause. To test for an INSERT or UPDATE action for more than one column, specify a separate UPDATE(column) clause following the first one. IF UPDATE will return the TRUE value in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.
Note The IF UPDATE (column) clause functions identically to an IF, IF...ELSE or WHILE statement and can use the BEGIN...END block. For more information, see Control-of-Flow Language.
UPDATE(column) can be used anywhere inside the body of the trigger.
column
Is the name of the column to test for either an INSERT or UPDATE action. This column can be of any data type supported by SQL Server. However, computed columns cannot be used in this context. For more information, see Data Types.
For example you have a table tblEmpoyees with a column of lastname, it would be something like this.
CREATE TRIGGER tr_SetMaiden ON dbo.tblEmployees
FOR UPDATE
AS
IF UPDATED(lastname)
BEGIN
INSERT INTO tblMaiden ([ID], MaidenName) SELECT [ID], LastName FROM deleted
END
Or something similar.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 19, 2002 at 3:28 pm
Thank you very much, Sir.
This information will help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply