错了呗.示例
A. 使用带有提醒消息的触发器
当有人试图在 titles 表中添加或更改数据时,下例将向客户端显示一条消息。说明 消息 50009 是 sysmessages 中的用户定义消息。有关创建用户定义消息的更多信息,请参见 sp_addmessage。
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'reminder' AND type = 'TR')
DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE
AS RAISERROR (50009, 16, 10)
GOB. 使用带有提醒电子邮件的触发器
当 titles 表更改时,下例将电子邮件发送给指定的人员 (MaryM)。USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'reminder' AND type = 'TR')
DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail 'MaryM',
'Don''t forget to print a report for the distributors.'
GOC. 在 employee 和 jobs 表之间使用触发器业务规则
由于 CHECK 约束只能引用定义了列级或表级约束的列,表间的任何约束(在下例中是指业务规则)都必须定义为触发器。下例创建一个触发器,当插入或更新雇员工作级别 (job_lvls) 时,该触发器检查指定雇员的工作级别(由此决定薪水)是否处于为该工作定义的范围内。若要获得适当的范围,必须引用 jobs 表。USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'employee_insupd' AND type = 'TR')
DROP TRIGGER employee_insupd
GO
CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @min_lvl tinyint,
@max_lvl tinyint,
@emp_lvl tinyint,
@job_id smallint
SELECT @min_lvl = min_lvl,
@max_lvl = max_lvl,
@emp_lvl = i.job_lvl,
@job_id = i.job_id
FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id
JOIN jobs j ON j.job_id = i.job_id
IF (@job_id = 1) and (@emp_lvl <> 10)
BEGIN
RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
ROLLBACK TRANSACTION
END
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
BEGIN
RAISERROR ('The level for job_id:%d should be between %d and %d.',
16, 1, @job_id, @min_lvl, @max_lvl)
ROLLBACK TRANSACTION
ENDD. 使用延迟名称解析
下例创建两个触发器以说明延迟名称解析。 USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'trig1' AND type = 'TR')
DROP TRIGGER trig1
GO
-- Creating a trigger on a nonexistent table.
CREATE TRIGGER trig1
on authors
FOR INSERT, UPDATE, DELETE
AS
SELECT a.au_lname, a.au_fname, x.info
FROM authors a INNER JOIN does_not_exist x
ON a.au_id = x.au_id
GO
-- Here is the statement to actually see the text of the trigger.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = 'TR' and o.name = 'trig1'-- Creating a trigger on an existing table, but with a nonexistent
-- column.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'trig2' AND type = 'TR')
DROP TRIGGER trig2
GO
CREATE TRIGGER trig2
ON authors
FOR INSERT, UPDATE
AS
DECLARE @fax varchar(12)
SELECT @fax = phone
FROM authors
GO
-- Here is the statement to actually see the text of the trigger.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = 'TR' and o.name = 'trig2'E. 使用 COLUMNS_UPDATED
下例创建两个表:一个 employeeData 表和一个 auditEmployeeData 表。人力资源部的成员可以修改 employeeData 表,该表包含敏感的雇员薪水信息。如果更改了雇员的社会保险号码 (SSN)、年薪或银行帐户,则生成审核记录并插入到 auditEmployeeData 审核表。通过使用 COLUMNS_UPDATED() 功能,可以快速测试对这些包含敏感雇员信息的列所做的更改。只有在试图检测对表中的前 8 列所做的更改时,COLUMNS_UPDATED() 才起作用。USE pubs
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'employeeData')
DROP TABLE employeeData
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'auditEmployeeData')
DROP TABLE auditEmployeeData
GO
CREATE TABLE employeeData (
emp_id int NOT NULL,
emp_bankAccountNumber char (10) NOT NULL,
emp_salary int NOT NULL,
emp_SSN char (11) NOT NULL,
emp_lname nchar (32) NOT NULL,
emp_fname nchar (32) NOT NULL,
emp_manager int NOT NULL
)
GO
CREATE TABLE auditEmployeeData (
audit_log_id uniqueidentifier DEFAULT NEWID(),
audit_log_type char (3) NOT NULL,
audit_emp_id int NOT NULL,
audit_emp_bankAccountNumber char (10) NULL,
audit_emp_salary int NULL,
audit_emp_SSN char (11) NULL,
audit_user sysname DEFAULT SUSER_SNAME(),
audit_changed datetime DEFAULT GETDATE()
)
GO
CREATE TRIGGER updEmployeeData
ON employeeData
FOR update AS
/*Check whether columns 2, 3 or 4 has been updated. If any or all of columns 2, 3 or 4 have been changed, create an audit record. The bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14. To check if all columns 2, 3, and 4 are updated, use = 14 in place of >0 (below).*/ IF (COLUMNS_UPDATED() & 14) > 0
/*Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of columns 2, 3, and 4 are updated.*/
BEGIN
-- Audit OLD record.
INSERT INTO auditEmployeeData
(audit_log_type,
audit_emp_id,
audit_emp_bankAccountNumber,
audit_emp_salary,
audit_emp_SSN)
SELECT 'OLD',
del.emp_id,
del.emp_bankAccountNumber,
del.emp_salary,
del.emp_SSN
FROM deleted del-- Audit NEW record.
INSERT INTO auditEmployeeData
(audit_log_type,
audit_emp_id,
audit_emp_bankAccountNumber,
audit_emp_salary,
audit_emp_SSN)
SELECT 'NEW',
ins.emp_id,
ins.emp_bankAccountNumber,
ins.emp_salary,
ins.emp_SSN
FROM inserted ins
END
GO/*Inserting a new employee does not cause the UPDATE trigger to fire.*/
INSERT INTO employeeData
VALUES ( 101, 'USA-987-01', 23000, 'R-M53550M', N'Mendel', N'Roland', 32)
GO/*Updating the employee record for employee number 101 to change the salary to 51000 causes the UPDATE trigger to fire and an audit trail to be produced.*/UPDATE employeeData
SET emp_salary = 51000
WHERE emp_id = 101
GO
SELECT * FROM auditEmployeeData
GO/*Updating the employee record for employee number 101 to change both the bank account number and social security number (SSN) causes the UPDATE trigger to fire and an audit trail to be produced.*/UPDATE employeeData
SET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M'
WHERE emp_id = 101
GO
SELECT * FROM auditEmployeeData
GOF. 使用 COLUMNS_UPDATED 测试 8 列以上
如果必须测试影响到表中前 8 列以外的列的更新时,必须使用 UBSTRING 函数测试由 COLUMNS_UPDATED 返回的适当的位。下例测试影响 Northwind.dbo.Customers 表中的第 3、第 5 或第 9 列的更新。USE Northwind
DROP TRIGGER tr1
GO
CREATE TRIGGER tr1 ON Customers
FOR UPDATE AS
IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))
+ power(2,(5-1)))
AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)))
)
PRINT 'Columns 3, 5 and 9 updated'
GOUPDATE Customers
SET ContactName=ContactName,
Address=Address,
Country=Country
GO
A. 使用带有提醒消息的触发器
当有人试图在 titles 表中添加或更改数据时,下例将向客户端显示一条消息。说明 消息 50009 是 sysmessages 中的用户定义消息。有关创建用户定义消息的更多信息,请参见 sp_addmessage。
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'reminder' AND type = 'TR')
DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE
AS RAISERROR (50009, 16, 10)
GOB. 使用带有提醒电子邮件的触发器
当 titles 表更改时,下例将电子邮件发送给指定的人员 (MaryM)。USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'reminder' AND type = 'TR')
DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail 'MaryM',
'Don''t forget to print a report for the distributors.'
GOC. 在 employee 和 jobs 表之间使用触发器业务规则
由于 CHECK 约束只能引用定义了列级或表级约束的列,表间的任何约束(在下例中是指业务规则)都必须定义为触发器。下例创建一个触发器,当插入或更新雇员工作级别 (job_lvls) 时,该触发器检查指定雇员的工作级别(由此决定薪水)是否处于为该工作定义的范围内。若要获得适当的范围,必须引用 jobs 表。USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'employee_insupd' AND type = 'TR')
DROP TRIGGER employee_insupd
GO
CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @min_lvl tinyint,
@max_lvl tinyint,
@emp_lvl tinyint,
@job_id smallint
SELECT @min_lvl = min_lvl,
@max_lvl = max_lvl,
@emp_lvl = i.job_lvl,
@job_id = i.job_id
FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id
JOIN jobs j ON j.job_id = i.job_id
IF (@job_id = 1) and (@emp_lvl <> 10)
BEGIN
RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
ROLLBACK TRANSACTION
END
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
BEGIN
RAISERROR ('The level for job_id:%d should be between %d and %d.',
16, 1, @job_id, @min_lvl, @max_lvl)
ROLLBACK TRANSACTION
ENDD. 使用延迟名称解析
下例创建两个触发器以说明延迟名称解析。 USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'trig1' AND type = 'TR')
DROP TRIGGER trig1
GO
-- Creating a trigger on a nonexistent table.
CREATE TRIGGER trig1
on authors
FOR INSERT, UPDATE, DELETE
AS
SELECT a.au_lname, a.au_fname, x.info
FROM authors a INNER JOIN does_not_exist x
ON a.au_id = x.au_id
GO
-- Here is the statement to actually see the text of the trigger.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = 'TR' and o.name = 'trig1'-- Creating a trigger on an existing table, but with a nonexistent
-- column.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'trig2' AND type = 'TR')
DROP TRIGGER trig2
GO
CREATE TRIGGER trig2
ON authors
FOR INSERT, UPDATE
AS
DECLARE @fax varchar(12)
SELECT @fax = phone
FROM authors
GO
-- Here is the statement to actually see the text of the trigger.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = 'TR' and o.name = 'trig2'E. 使用 COLUMNS_UPDATED
下例创建两个表:一个 employeeData 表和一个 auditEmployeeData 表。人力资源部的成员可以修改 employeeData 表,该表包含敏感的雇员薪水信息。如果更改了雇员的社会保险号码 (SSN)、年薪或银行帐户,则生成审核记录并插入到 auditEmployeeData 审核表。通过使用 COLUMNS_UPDATED() 功能,可以快速测试对这些包含敏感雇员信息的列所做的更改。只有在试图检测对表中的前 8 列所做的更改时,COLUMNS_UPDATED() 才起作用。USE pubs
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'employeeData')
DROP TABLE employeeData
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'auditEmployeeData')
DROP TABLE auditEmployeeData
GO
CREATE TABLE employeeData (
emp_id int NOT NULL,
emp_bankAccountNumber char (10) NOT NULL,
emp_salary int NOT NULL,
emp_SSN char (11) NOT NULL,
emp_lname nchar (32) NOT NULL,
emp_fname nchar (32) NOT NULL,
emp_manager int NOT NULL
)
GO
CREATE TABLE auditEmployeeData (
audit_log_id uniqueidentifier DEFAULT NEWID(),
audit_log_type char (3) NOT NULL,
audit_emp_id int NOT NULL,
audit_emp_bankAccountNumber char (10) NULL,
audit_emp_salary int NULL,
audit_emp_SSN char (11) NULL,
audit_user sysname DEFAULT SUSER_SNAME(),
audit_changed datetime DEFAULT GETDATE()
)
GO
CREATE TRIGGER updEmployeeData
ON employeeData
FOR update AS
/*Check whether columns 2, 3 or 4 has been updated. If any or all of columns 2, 3 or 4 have been changed, create an audit record. The bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14. To check if all columns 2, 3, and 4 are updated, use = 14 in place of >0 (below).*/ IF (COLUMNS_UPDATED() & 14) > 0
/*Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of columns 2, 3, and 4 are updated.*/
BEGIN
-- Audit OLD record.
INSERT INTO auditEmployeeData
(audit_log_type,
audit_emp_id,
audit_emp_bankAccountNumber,
audit_emp_salary,
audit_emp_SSN)
SELECT 'OLD',
del.emp_id,
del.emp_bankAccountNumber,
del.emp_salary,
del.emp_SSN
FROM deleted del-- Audit NEW record.
INSERT INTO auditEmployeeData
(audit_log_type,
audit_emp_id,
audit_emp_bankAccountNumber,
audit_emp_salary,
audit_emp_SSN)
SELECT 'NEW',
ins.emp_id,
ins.emp_bankAccountNumber,
ins.emp_salary,
ins.emp_SSN
FROM inserted ins
END
GO/*Inserting a new employee does not cause the UPDATE trigger to fire.*/
INSERT INTO employeeData
VALUES ( 101, 'USA-987-01', 23000, 'R-M53550M', N'Mendel', N'Roland', 32)
GO/*Updating the employee record for employee number 101 to change the salary to 51000 causes the UPDATE trigger to fire and an audit trail to be produced.*/UPDATE employeeData
SET emp_salary = 51000
WHERE emp_id = 101
GO
SELECT * FROM auditEmployeeData
GO/*Updating the employee record for employee number 101 to change both the bank account number and social security number (SSN) causes the UPDATE trigger to fire and an audit trail to be produced.*/UPDATE employeeData
SET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M'
WHERE emp_id = 101
GO
SELECT * FROM auditEmployeeData
GOF. 使用 COLUMNS_UPDATED 测试 8 列以上
如果必须测试影响到表中前 8 列以外的列的更新时,必须使用 UBSTRING 函数测试由 COLUMNS_UPDATED 返回的适当的位。下例测试影响 Northwind.dbo.Customers 表中的第 3、第 5 或第 9 列的更新。USE Northwind
DROP TRIGGER tr1
GO
CREATE TRIGGER tr1 ON Customers
FOR UPDATE AS
IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))
+ power(2,(5-1)))
AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)))
)
PRINT 'Columns 3, 5 and 9 updated'
GOUPDATE Customers
SET ContactName=ContactName,
Address=Address,
Country=Country
GO
解决方案 »
- sql case when
- 存储过程 批量更新
- 请问SQL2005下,怎么将一个实例与ip地址关联
- 如何生成现有表中数据的sql语法?
- 为什么没有删除调全局临时表?
- 存储过过程不能执行??高手指教,高分
- *****班级名次,年级名次问题******
- 小问题一个,关于sql server数据库-->所有任务-->导出数据库到底是怎么用的呀?
- SQL Sever的企业管理器突然出现SQLDMO未注册,重装了好几次还是一样,用RegSvr32 sqldmo.dll出现SQLDMO.dll中的DLLRegisterSever失败,返
- INTERSECT 在SQL SERVERS7.0中的用法???
- ===救命呀~~~`连接远程数据源的问题~~~~~~(在线等...直到解决)
- 存储过程的所有者都为空了,这是怎么事?
说这么多地球以外的事情跟人类有关系吗?