CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}
CREATE TABLE Company(
CompanyID varchar (10) NOT NULL ,
CompanyName varchar (30) NOT NULL
)
CREATE TABLE Contract (
ContractID varchar (10) NOT NULL ,
CompanyID varchar (10) NOT NULL ,
ContractName varchar (30) NULL ,
ContractVolume numeric(18, 2) NULL,
SignDate Datetime NULL)
CREATE TABLE ContractDetail (
ContractID varchar (10) NOT NULL ,
ContractDetailID varchar (10) NOT NULL ,
Volume numeric(18, 2) NULL)
CREATE TRIGGER attetion
ON Company
FOR INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail 'RED', ‘公司信息表已发生变化!’
--修改触发器用ALTER TRIGGER,语法如下:
ALTER TRIGGER trigger_name
ON ( table | view )
[ WITH ENCRYPTION ]
{
{ ( FOR | AFTER | INSTEAD OF ) { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
[ NOT FOR REPLICATION ]
AS
sql_statement [ ...n ]
}
|
{ ( FOR | AFTER | INSTEAD OF ) { [ INSERT ] [ , ] [ UPDATE ] }
[ NOT FOR REPLICATION ]
AS
{ IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
}
sql_statement [ ...n ]
}
}