SQL-UPDATE触发器练习

&练习一

如下所示三张表（ student，grade，student_updata_before ）：

student表

grade表

Student_update_before表

# 触发器需要实现一下要求：修改学生信息后，将修改前的学生信息添加到student_updata_before表中，并记录操作的用户ip和修改时间。

触发器如下（如有问题，欢迎指出）：

CREATE TRIGGER Up_stu
ON student
FOR UPDATE
AS
IF ( SELECT COUNT(1) FROM inserted ) > 1
BEGIN
    RAISERROR('每次只能更新一条数据',16,10);
    ROLLBACK;
END
ELSE
BEGIN
    DECLARE @Ubsid int,@O_sid int,@O_sname varchar(20),@O_sage int,@O_sgname varchar(20),@inf varchar(20),@test varchar(100);
    SELECT @inf = USER_NAME();
    SELECT TOP 1 @O_sid=Sid,@O_sname=Sname,@O_sage=Sage,@O_sgname=Sg_name FROM deleted;
    SET @test ='USER:'+ @inf +'|Old Data:'+ CONVERT(varchar,@O_sid) +'/'+ @O_sname +'/'+ CONVERT(varchar,@O_sage) +'/'+ @O_sgname;
    IF (SELECT COUNT(1) FROM Student_update_before) = 0
        SET @Ubsid = 1;
    ELSE
        SET @Ubsid = (SELECT MAX(Ubsid) FROM Student_update_before) + 1;
    INSERT INTO Student_update_before VALUES(@Ubsid,@test,GETDATE());
END
GO

#  触发器需要实现一下要求：修改学生信息时，不能修改学生的学号信息。

触发器如下（如有问题，欢迎指出）：

CREATE TRIGGER tgr_student_up_column
ON student
FOR UPDATE
AS
--列级触发器：是否更新了学生学号
IF (UPDATE(Sid))
BEGIN
    RAISERROR('系统提示：学生学号不能修改！', 16, 11);
    rollback;
END
go

 

&练习二

#准备工作：创建员工employee表、部门变动历史dept_history表，工资变动历史sal_history表

#1,创建员工employee表:

CREATE TABLE employee(
    eid varchar(20) PRIMARY KEY,
    ename varchar(20),
    dept varchar(20),
    salary int,
    uptime date,
    status int   -- 状态(1表示在职，0表示离职)
)
go

#2,创建部门变动历史dept_history表

CREATE TABLE dept_history(
    did int identity(1,1) primary key,
    eid varchar(20),
    olddept varchar(20),
    newdept varchar(20),
    uptime date,
    FOREIGN KEY(eid) REFERENCES employee(eid)
)
go

#3，创建工资变动历史sal_history表

CREATE TABLE sal_history(
    sid int identity(1,1) primary key,
    eid varchar(20),
    oldsal varchar(20),
    newsal varchar(20),
    uptime date,
    FOREIGN KEY(eid) REFERENCES employee(eid)
)
go

触发器实现功能：当新职工入职时，员工信息表将插入1条数据。同时，触发器在部门变动历史中增加1条记录，其中olddept值为null；在工资变动历史中增加1条记录，其中oldsal值为0。

触发器如下（如有问题，欢迎指出）：

CREATE TRIGGER Up_em
ON employee
FOR UPDATE
AS
IF ( SELECT COUNT(1) FROM DELETED ) > 1
BEGIN
    RAISERROR('每次只能更新一条数据！请重新输入！',16,10)
END
ELSE
BEGIN
    DECLARE @eid varchar(20),@olddept varchar(20),@newdept varchar(20),@oldsal int,@newsal int,@status int;
    SELECT TOP 1 @olddept = dept,@oldsal = salary FROM deleted;    
    SELECT TOP 1 @eid = eid,@newdept = dept,@newsal = salary,@status = status FROM inserted;
    IF NOT EXISTS (SELECT 1 FROM employee WHERE eid = @eid)    
    BEGIN
        RAISERROR('当前要更新的数据不存在，请插入后进行更新！',16,10);
        ROLLBACK;    
    END    
    ELSE 
    BEGIN
        IF @olddept <> @newdept OR @status = 0
            INSERT INTO  dept_history (eid,olddept,newdept,uptime) values(@eid,@olddept,@newdept,GETDATE());    
        IF     @oldsal <> @newsal OR @status = 0
            INSERT INTO  sal_history (eid,oldsal,newsal,uptime) values(@eid,@oldsal,@newsal,GETDATE());
    END        
END