基于触发器的事件捕获

--事件队列表

select * from t_sys_eventqueue;

--测试表

create table t_tst_test1 (
   objectid bigint not null identity(1,1),
   id int not null,
   name varchar(32) not null,
   status tinyint not null default 0
);

--insert触发器

drop trigger tr_insert_tst_test1;
create trigger tr_insert_tst_test1 on t_tst_test1 
for insert
as
BEGIN
declare @arg varchar(256);
select @arg=convert(varchar(20),objectid)+','+name from inserted;
insert into t_sys_eventqueue(eventid,createtime,eventpara) values(2053,getdate(),@arg);
END

--测试
insert into t_tst_test1(id,name) values(1,'dddd');

--update触发器

drop trigger tr_update_tst_test1;
create trigger tr_update_tst_test1 on t_tst_test1 
for update
as
BEGIN
declare @eventid int;
if (update(status)) ---状态改变产生不同的事件
begin
	declare @old_status int;
	declare @new_status int;
	select @old_status=status from Deleted;
	select @new_status=status from Inserted;
    if (@old_status=@new_status)
		return;
	set @eventid=2054;
end
else 
	set @eventid=2055

declare @arg varchar(256);
select @arg=convert(varchar(20),objectid)+','+name from inserted;
insert into t_sys_eventqueue(eventid,createtime,eventpara) values(@eventid,getdate(),@arg);
END

--测试

update t_tst_test1 set name='aaaa' where id=1;
update t_tst_test1 set status=1 where id=1;


--delete触发器

drop trigger tr_delete_tst_test1;
create trigger tr_delete_tst_test1 on t_tst_test1
for delete
as
begin
declare @arg varchar(256);
select @arg=convert(varchar(20),objectid)+','+name from inserted;
insert into t_sys_eventqueue(eventid,createtime,eventpara) values(2056,getdate(),@arg);
end

--测试

delete from t_tst_test1 where id=1;