-- TABLE INSERTVAL UPDATEVAL
if (object_id('DATA_SYNC_FH_DJ','TR') is not null)
drop trigger DATA_SYNC_FH_DJ
go
create trigger DATA_SYNC_FH_DJ
on FH_DJ
for insert,update,delete
as
declare
@oldUpdate varchar(20),
@newDate varchar(20),
@DJdanhao varchar(20),
@Djid int,
@isInsert bit,
@isUpdate bit,
@isDelete bit;
-- 判断是否为插入操作
IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
BEGIN
SET @isInsert = 1;
select @Djid = djid from inserted;
END
ELSE
SET @isInsert = 0
-- 判断是否为更新操作
IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
BEGIN
SET @isUpdate = 1;
select @Djid = djid from inserted;
END
ELSE
SET @isUpdate = 0
-- 判断是否为删除操作
IF (NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted))
BEGIN
SET @isDelete = 1;
select @DJdanhao = DJdanhao from deleted;
END
ELSE
SET @isDelete = 0
--更新前的数据
select @oldUpdate = F_SYNC_UPDATE from deleted;
--通过应用程序修改时,F_SYNC_UPDATE=null或F_SYNC_UPDATE=0,此时不需要更新F_SYNC_DATE 时间戳,也不需要记录删除记录
if ((@oldUpdate is null) or (@oldUpdate = 0))
begin
--更新操作,更新时间戳F_SYNC_DATE=systimestamp和F_SYNC_UPDATE=null
if (@isUpdate = 1)
insert into DATA_SYNC_B_OPERATOR (t_name, o_type, o_date, VKEYS)
values ('FH_DJ', 2, GETDATE(), @Djid);
--把新增加的记录插入到操作记录表
if (@isInsert = 1)
insert into DATA_SYNC_B_OPERATOR (t_name, o_type, o_date, VKEYS)
values ('FH_DJ', 1, GETDATE(), @Djid);
--把删除记录的主键添加到操作记录表
if (@isDelete = 1)
insert into DATA_SYNC_B_OPERATOR (t_name, o_type, o_date, VKEYS)
values ('FH_DJ', 3, GETDATE(), 'test@' + @DJdanhao);
end
go
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341