实验07 触发器

/**********************
实验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'