使用带有提醒消息的触发器(转)

. 使用带有提醒消息的触发器
当有人试图在 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 )
GO

B. 使用带有提醒电子邮件的触发器
当 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. '
GO

C. 在 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
END

D. 使用延迟名称解析
下例创建两个触发器以说明延迟名称解析。

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
GO

F. 使用 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 '
GO

UPDATE Customers
   SET ContactName = ContactName,
      Address = Address,
      Country = Country
GO
 

最后

以上就是娇气黑夜为你收集整理的使用带有提醒消息的触发器(转)的全部内容，希望文章能够帮你解决使用带有提醒消息的触发器(转)所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。