• 16、SQL基础整理(触发器.方便备份)


    触发器(方便备份)

    本质上还是一个存储过程,只不过不是通过exec来调用执行,而是通过增删改数据库的操作来执行(可以操作视图)

     

    全部禁用触发器

    alter table teacher disable trigger all

    全部开启触发器

    alter table teacher enable trigger all

    create trigger TR_student_delete--默认触发器名

    on student --在哪个表上操作

    instead of delete --为了对表做什么而建的,‘for’是在执行完外部语句之后用,‘instead of’是直接将要执行的语句替换为执行as里面的语句

    as

        delete from score where sno =108

        delete from student where Sno = 108

    go

    delete from student where Sno = 108

     

    select *from student

    108行被删掉

    alter trigger TR_student_delete--默认触发器名

    on student --在哪个表上操作

    for delete

    as

        insert into student values(108,'曾鹏','男',1905-05-22,95033)

    go

    delete from student where Sno = 108

     

    select *from student

    *曾华变为了曾鹏

    动态触发

    create trigger dongtaichufa

    on teacher

    instead of delete

    as

    begin

        declare @tno varchar(20)

        set @tno = (select tno from deleted)—-选择执行语句中的tno所在@tno

        update teacher set tname='张三'where tno = @tno

    end

    go

    delete from teacher where Tno='831'

    deleted存储删除后的信息,inserted存放更改后的信息。

    执行delete语句时的tno=触发器里面的tno,而不是@tno

     

    create trigger TR_teacher_insert

    on teacher

    for insert

    as

    begin

        declare @tno varchar(20)

        set @tno=(select tno from inserted)

        ---delete from teacher where tno=@tno

        declare @sex varchar(10)

        set @sex =(select tsex from teacher where tno=@tno)

        if(@sex='男')

        update teacher set tsex='女'where tno =@tno

        else

        update teacher set tsex='男'where tno =@tno

    end

    go

    insert into teacher values (800,'王五','男',1990-09-09,'教授','计算机系')

     

  • 相关阅读:
    extern--C#调用C++等其他非托管代码
    unhandledException详细介绍
    MySql如何安装?
    Mindoc搭建流程
    反射_IsDefined判断方法上有自定义的标签
    WebApi_返回Post格式数据
    编码
    IP地址与MAC地址
    Tcp/Ip:Telnet指令
    create-react-app使用的问题
  • 原文地址:https://www.cnblogs.com/wleaves/p/4165147.html
Copyright © 2020-2023  润新知