/**********************
实验07 触发器
**********************/
--【实验内容】
--1.为Users表创建一个触发器,不允许插入名为admin或“管理员”的用户,也不允许将用户名改为admin或“管理员”
--AFTER
CREATE TRIGGER Insert_Or_Update_After_UserAdmin
ON USERS
AFTER INSERT,UPDATE
AS
DECLARE @UserName VARCHAR(20), @UserRealName VARCHAR(20);
DECLARE @NewUserName VARCHAR(20), @NewUserRealName VARCHAR(20);
DECLARE @CountAdmin INT;
SELECT @UserName = UserName FROM DELETED;
SELECT @NewUserName = UserName FROM INSERTED;
PRINT(@UserName)
SELECT @CountAdmin = COUNT(*) FROM Users WHERE UserName = 'admin' or UserName = '管理员'
IF (@NewUserName = 'admin' OR @NewUserName = '管理员') AND (@UserName is null)
BEGIN
DELETE FROM Users WHERE UserName = @NewUserName
END
ELSE IF @CountAdmin > 0
BEGIN
UPDATE Users SET UserName = @UserName WHERE UserName = @NewUserName;
END
--两种实现方式:1、判定数据库里面有没有admin;2、判定@UserName is null,not null表示这是个update操作
--DROP TRIGGER Insert_Or_Update_After_UserAdmin
--INSTEAD OF
CREATE TRIGGER Insert_Or_Update_Instead_UserAdmin
ON USERS
INSTEAD OF INSERT,UPDATE
AS
DECLARE @UserName VARCHAR(20), @UserRealName VARCHAR(20);
DECLARE @UserPassword VARCHAR(6), @UserSex CHAR(2), @UserAgeRange CHAR(8), @UserAddress VARCHAR(256);
DECLARE @UserPostCode CHAR(6), @UserPhone VARCHAR(32), @UserEmail VARCHAR(50);
DECLARE @UserRegisterTime SMALLDATETIME, @UserAdvancePayment NUMERIC(8, 2);
DECLARE @OldUserName VARCHAR(20);
SELECT @UserName = UserName, @UserRealName = UserRealName,
@UserPassword = UserPassword, @UserSex = UserSex, @UserAgeRange = UserAgeRange,
@UserAddress = UserAddress, @UserPostCode = UserPostCode, @UserPhone = UserPhone, @UserEmail = UserEmail,
@UserRegisterTime = UserRegisterTime, @UserAdvancePayment = UserAdvancePayment FROM INSERTED;
SELECT @OldUserName = UserName FROM DELETED;
IF (@UserName != 'admin' AND @UserName != '管理员')
BEGIN
IF(@OldUserName IS NULL)--代表这是一个插入操作
BEGIN
PRINT('INSERT')
INSERT INTO Users (UserName,UserPassword,UserSex,UserRealName,UserAgeRange,
UserAddress,UserPostCode,UserPhone,UserEmail, UserRegisterTime, UserAdvancePayment)
Values(@UserName, @UserPassword, @UserSex, @UserRealName, @UserAgeRange,
@UserAddress, @UserPostCode, @UserPhone, @UserEmail, @UserRegisterTime, @UserAdvancePayment)
END
ELSE
BEGIN
PRINT('UPDATE')
UPDATE Users SET UserName = @UserName, UserPassword = @UserPassword, UserSex = @UserSex,
UserRealName = @UserRealName, UserAgeRange = @UserAgeRange, UserAddress = @UserAddress,
UserPostCode = @UserPostCode, UserPhone = @UserPhone, UserEmail = @UserEmail, UserRegisterTime = @UserRegisterTime,
UserAdvancePayment = @UserAdvancePayment
WHERE UserName = @OldUserName
END
END
ELSE
PRINT('REFUSE')
--DROP TRIGGER Insert_Or_Update_Instead_UserAdmin
--Test Code
INSERT INTO Users(UserName,UserPassword,UserSex,UserRealName,UserAgeRange,UserAddress,UserPostCode,UserPhone,UserEmail)
VALUES('admin1','123','女','管理员1','21~30岁','北京海淀区中关村','100098','18611983575','wxy@cuc.edu.cn')
UPDATE Users SET UserName = 'admin' WHERE UserName = 'admin1'
UPDATE Users SET UserRealName = '管理员2' WHERE UserName = 'admin1'
实验07 触发器
最新推荐文章于 2023-11-29 14:14:51 发布