sqlserver 触发器示例

代码如下：

1 -- 检查当前触发器是否已存在
2 IF exists ( SELECT * FROM sysobjects WHERE xtype = ' TR ' AND [ name ] = ' TR_INSERTUserInfo_LoginLog ' )
3 -- 存在即删除该触发器
4 DROP TRIGGER TR_INSERTUserInfo_LoginLog
5 go
6 -- 触发器创建在UserInfo表上 当对UserInfo表执行INSERT操作后 自动执行触发器中的SQL语句
7 CREATE TRIGGER TR_INSERTUserInfo_LoginLog
8 ON UserInfo
9 FOR INSERT
10 AS
11 BEGIN
12 -- 定义接受新建用户ID的参数
13 DECLARE @userID VARCHAR ( 50 );
14 -- 查询INSERTED临时表获取新建用户ID
15 SELECT @userID = UserID FROM Inserted
16 -- 向用户登录日志表中添加新建用户登录日志
17 INSERT INTO LoginLog VALUES ( @userID , getDate ())
18 END
19 GO
20
21 IF EXISTS ( SELECT * FROM sysobjects WHERE xtype = ' TR ' AND [ name ] = ' TR_Update_UserInfo_ManagerLog ' )
22 DROP TRIGGER TR_Update_UserInfo_ManagerLog
23 GO
24 CREATE TRIGGER TR_Update_UserInfo_ManagerLog
25 ON UserInfo
26 FOR UPDATE
27 AS
28 -- 接受被更新用户信息的ID
29 DECLARE @userId INT
30 -- 接受更新前用户密码的变量
31 DECLARE @oldPwd VARCHAR ( 50 )
32 -- 接受更新后用户密码的变量
33 DECLARE @newPwd VARCHAR ( 50 )
34 -- 接受更新前用户邮件的变量
35 DECLARE @oldEmail VARCHAR ( 50 )
36 -- 接受更新后用户邮件的变量
37 DECLARE @newEmail VARCHAR ( 50 )
38 -- 从DELETED临时表中获取数据更新前用户数据
39 SELECT @userId = UserId, @oldPwd = password, @oldEmail = Email FROM Deleted
40 -- 从INSERTED临时表中获取数据更新后的用户数据
41 SELECT @newPwd = password, @newEmail = Email FROM Inserted
42 -- 向系统日志表中插入数据
43 INSERT INTO managerlog VALUES ( ' 修改ID为：[ ' + CAST ( @userId AS VARCHAR ( 5 )) + ' ]用户信息：<br/>Password:{ ' + @oldPwd + ' }-->Password:{ ' + @newPwd + ' }<br/>Email:{ ' + @oldEmail + ' }-->{ ' + @newEmail + ' } ' , getDate ())
44 Go
45
46
47 -- 创建当用户表插入新数据时，添加工资表对应记录的触发器
48 IF EXISTS ( SELECT * FROM sysobjects WHERE [ xtype ] = ' TR ' AND [ name ] = ' Insert_Emp_EmpPayment ' )
49 DROP TRIGGER Insert_Emp_EmpPayment
50 go
51 CREATE TRIGGER Insert_Emp_EmpPayment
52 ON Employee1
53 FOR INSERT
54 AS
55 DECLARE @empId INT
56 SELECT @empId = EmpId FROM Inserted
57 INSERT INTO EmpPayMent VALUES ( @empId , 800.00 , null )
58 go
59
60 -- 创建当向工资表插入数据时，更新工资表中员工奖金
61 IF EXISTS ( SELECT * FROM sysobjects WHERE [ xtype ] = ' TR ' AND [ name ] = ' Insert_EmpPayment ' )
62 DROP TRIGGER Insert_EmpPayment
63 go
64 CREATE TRIGGER Insert_EmpPayment
65 ON EmpPayMent
66 FOR INSERT
67 AS
68 DECLARE @empId INT
69 SELECT @empId = EmpId FROM Inserted
70 UPDATE EmpPayMent SET Bonus = 50.00 WHERE EmpID = @empId
71 go

转载于:https://www.cnblogs.com/yerenno1/articles/2036234.html