Trigger For MSSQL

最新推荐文章于 2024-03-12 10:28:54 发布

苏州牛恋歌

最新推荐文章于 2024-03-12 10:28:54 发布

阅读量891

点赞数

分类专栏： MsSql 文章标签： encryption insert assembly delete table schema

本文链接：https://blog.csdn.net/niuzai520/article/details/7431893

版权

MsSql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

1.语法：

Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
CREATE TRIGGER [ schema_name . ]trigger_name 
ON { table | view } 
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
[ NOT FOR REPLICATION ] 
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

<dml_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

2.举个例子：XXX人打卡,如果八点半后打卡就算迟到，所以建立个触发器，打卡后(for【默认After,可以不写】)判断打卡时间，如果大于八点半则插入后把这条记录的打卡时间改成八点半，可以定义到八点半到九点半之内打卡都不算迟到、大家看例子后都不要学着例子做哦、谢谢支持了

if object_id('NLate') is not null 
    drop trigger NLate
go
create trigger NLate on CHECKINOUT --[on 表名]
    for insert  
as
    declare @LateTime datetime      --打卡时间[已迟到]
    declare @NLateTime datetime     --不迟到时间1
    declare @NLeaveTime datetime    --不迟到时间2 
    select  @LateTime = CHECKTIME ,
            @NLateTime = cast(( left(CHECKTIME, 10) + ' 08:30'
                                + right(convert(char(25), getdate(), 121), 9) ) as datetime),
            @NLeaveTime = cast(( left(CHECKTIME, 10) + ' 09:30'
                                + right(convert(char(25), getdate(), 121), 9) ) as datetime)
    from    inserted  where   USERID in ( select  userid
                                          from    UserInfo
                                          where   BadgeNumber in ( '006514' ) )
    if (@LateTime > @NLateTime and @LateTime<@NLeaveTime)
        begin 
            update  dbo.CHECKINOUT
            set     CHECKTIME = @NLateTime
            where   CHECKTIME = @LateTime
                    and USERID in ( select  userid
                                    from    UserInfo
                                    where   BadgeNumber in ( '006514' ) )
        end

推荐博客：AnCode++ （VS2017/19 插件）