SQL-INSERT触发器练习

&练习一

有这样的一个基础表A，字段包括：id、type、value、create_time，主要是记录某个类型的状态变化时间和值。在插入类型（type）为‘runtime’ 的数据时，根据前后变化的值，更新一条该type的前后两条时间组合的信息到另一张表B。

定义：value为布尔型，true-->false时，为停止时间，false-->true时，为运行时间

具体要求：表A插入一条runstate的数据时，需要在表B插入一条停止或运行的信息。

eg：

A表

B表

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

CREATE TRIGGER RUN_STATE
ON A
FOR INSERT
AS
    IF( (SELECT COUNT(1) FROM inserted) > 1 )
    BEGIN
        RAISERROR('一次只能插入一条数据！',16,10);
        rollback;
    END
    ELSE
    BEGIN
        IF( (SELECT TOP 1 type FROM inserted)='runtime' ) ----插入的数据是否为runtime状态信息
        BEGIN
            IF EXISTS ( SELECT 1 FROM B WHERE type = 'runtime') -----B表中是否已经创建了runtime信息表
            BEGIN
                DECLARE @Old_state varchar(10),@create_time smalldatetime;
                SET @Old_state = (SELECT state FROM B WHERE type = 'runtime');
                SET @Create_time = (SELECT TOP 1 create_time FROM inserted);
                if @Old_state = '运行' 
                    BEGIN
                    update B SET end_time = @Create_time WHERE type = 'runtime';
                    update B SET state = @Create_time WHERE type = '停止';
                    END
                ELSE
                    BEGIN
                    update B SET start_time = @Create_time WHERE type = 'runtime';
                    update B SET state = @Create_time WHERE type = '运行';
                    END
            END
            else
            BEGIN
                DECLARE @id int,@type varchar(20),@value bit;
                SELECT TOP 1 @id=id,@type=type,@value=value,@create_time=create_time FROM inserted;
                IF @value = 1
                    INSERT B (id,type,start_time,state) values(@id,'runtime',@create_time,'运行');
                ELSE
                    INSERT B (id,type,end_time,state) values(@id,'runtime',@create_time,'停止');        
            END
        END
    END
GO

 

&练习二

需要用到的表如下（卷烟库存表【CI_list】，卷烟销售表【CS_list】）：

--业务规则：库存金额 = 库存数量 * 库存单价 

--业务规则：销售金额 = 销售数量 * 销售单价

eg1：强制执行业务规则，保证插入的数据中，库存金额 = 库存数量 * 库存单价，且库存金额不为空

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

CREATE TRIGGER T_INSERT_CS
ON CS_list
FOR INSERT
AS
DECLARE @i_num int,@i_uprice money,@i_amount money;
IF ( (SELECT COUNT(*) FROM inserted)>1 )
BEGIN
    RAISERROR('一次只能插入一行数据',16,10);
    ROLLBACK;
END
ELSE
BEGIN
    SELECT TOP 1 @i_num=i_num,@i_uprice=i_uprice,@i_amount=i_amount FROM inserted;
    IF ISNULL(@i_amount,1)=1 OR @i_amount != @i_num*@i_uprice
    BEGIN
        RAISERROR('插入的数据不符合业务规则！已根据规则修改数据',16,10);
        UPDATE CS_list SET i_amount = @i_num*@i_uprice WHERE cs_brand IN (SELECT TOP 1 cs_brand FROM inserted)
    END
END
GO

INSERT INTO CS_list values('红塔山新势力',2,30,60);
INSERT INTO CS_list (cs_brand,i_num,i_uprice) values('红1',2,20);
INSERT INTO CS_list values('云南映像',2,30,70);
GO

eg2：如果销售的卷烟品牌不存在库存或者库存为零，则返回错误；否则则自动减少[卷烟库存表]中对应品牌卷烟的库存数量和库存金额。并且满足业务规则

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

 

CREATE TRIGGER T_INSERT_CI
ON CI_list
FOR INSERT
AS
IF ( (SELECT COUNT(*) FROM inserted)>1 )
BEGIN
    RAISERROR('一次只能插入一行数据',16,10);
    ROLLBACK;
END
ELSE
BEGIN
    DECLARE @CI_BRAND varchar(40),@CS_BRAND varchar(40);
    DECLARE @s_num int,@s_uprice money,@s_amount money;
    SELECT TOP 1 @CI_BRAND=ci_brand,@s_num=s_num,@s_uprice=s_uprice,@s_amount=s_amount FROM inserted;
    IF not EXISTS (SELECT 1 FROM CS_list WHERE cs_brand=@CI_BRAND)
    BEGIN17         RAISERROR('库存中不含当前要销售的卷烟',16,10);
        ROLLBACK;    
    END    
    ELSE IF (SELECT i_num FROM CS_list WHERE cs_brand=@CI_BRAND) = 0
    BEGIN
        RAISERROR('库存中已经没有当前要销售的香烟',16,10);
        ROLLBACK;    
    END    
    ELSE
    BEGIN
        IF ISNULL(@s_amount,1)=1 OR @s_amount != @s_num*@s_uprice
        BEGIN
            RAISERROR('插入的数据不符合业务规则！已根据规则修改数据',16,10);
            UPDATE CI_list SET s_amount = @s_num*@s_uprice WHERE ci_brand = (SELECT TOP 1 ci_brand FROM inserted)
        END
        UPDATE CS_list SET i_num=i_num-@s_num WHERE cs_brand = (SELECT TOP 1 ci_brand FROM inserted);
        UPDATE CS_list SET i_amount=i_num*i_uprice WHERE cs_brand = (SELECT TOP 1 ci_brand FROM inserted);
    END
END

 

  &练习三

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

student表

grade表

Student_update_before表

触发器需要实现一下要求：

1. 如果学生所在的班级存在，则班级表学生数+1。

2. 如果学生所在的班级不存在，则在班级表中新建班级，并将该学生加入到该班级中。

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

CREATE TRIGGER Add_stu
ON student
FOR INSERT
AS
IF ( SELECT COUNT(1) FROM inserted ) > 1
BEGIN
    RAISERROR('每次只能插入一条数据',16,10);
    ROLLBACK;
END
ELSE
BEGIN
    DECLARE @Sg_name varchar(30);
    SELECT TOP 1 @Sg_name = Sg_name FROM inserted;
    IF EXISTS ( SELECT 1 FROM grade WHERE Gname = @Sg_name )
    BEGIN
        DECLARE @num1 int;
        SET @num1 = (SELECT COUNT(*) FROM student WHERE Sg_name = @Sg_name);
        UPDATE grade SET Gnum = @num1 WHERE Gname = @Sg_name;
    END
    ELSE
    BEGIN
        DECLARE @gid int;
        IF (SELECT COUNT(1) FROM grade) = 0
            SET @gid = 1;
        ELSE
            SET @gid = (SELECT MAX(Gid) FROM grade ) + 1;
        declare @num int;
        INSERT INTO grade VALUES(@gid,@Sg_name,1,'');
    END
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
)
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 Add_em
ON employee
FOR INSERT
AS
IF ( SELECT COUNT(1) FROM inserted ) > 1
BEGIN
    RAISERROR('每次只能插入一条数据！请重新输入！',16,10)
END
ELSE
BEGIN
    DECLARE @eid varchar(20),@dept varchar(20),@salary int;
    SELECT TOP 1 @eid = eid ,@dept = dept ,@salary  = salary FROM inserted;
    INSERT INTO  dept_history (eid,olddept,newdept,uptime) values(@eid,null,@dept,GETDATE());    
    INSERT INTO  sal_history (eid,oldsal,newsal,uptime) values(@eid,0,@salary,GETDATE());        
END