表A
create table a
(
id int primary key identity(1,1) not null,
TID nvarchar(50) not null,
type nvarchar(10) not null
)
表userinfo
create table userinfo
(
id int primary key identity(1,1) not null,
username nvarchar(50) not null
)
create trigger t on userinfo for update,insert,delete
as
将刚刚触发的事件表userinfo 的id 插入到 a表的TID中。
//得到刚刚触发事件的ID:select id from updated ||select id from inserted||deleted
将刚刚触发的事件的类型,比如是更新就插"insert"到 userinfo 的type表
//附加一问题
select * from (select b.id,b.username,c.id s from userinfo b left join a c on (b.id=c.id))
viewa where viewa.s is NULL
这个SQL语句在庞大的数据量的面前执行效率会怎么样?
十分感谢。
create table a
(
id int primary key identity(1,1) not null,
TID nvarchar(50) not null,
type nvarchar(10) not null
)
表userinfo
create table userinfo
(
id int primary key identity(1,1) not null,
username nvarchar(50) not null
)
create trigger t on userinfo for update,insert,delete
as
将刚刚触发的事件表userinfo 的id 插入到 a表的TID中。
//得到刚刚触发事件的ID:select id from updated ||select id from inserted||deleted
将刚刚触发的事件的类型,比如是更新就插"insert"到 userinfo 的type表
//附加一问题
select * from (select b.id,b.username,c.id s from userinfo b left join a c on (b.id=c.id))
viewa where viewa.s is NULL
这个SQL语句在庞大的数据量的面前执行效率会怎么样?
十分感谢。
是insert 就将"insert"插入到userinfo的type 表
on userinfo
for update,insert,delete
as
if not exists(select 1 from deleted) --insert
begin
insert a(tid,type) select id,'insert' from inserted;
end
if not exists(select 1 from inserted) --delete
begin
insert a(tid,type) select id,'delete' from deleted;
end
else --update
begin
insert a(tid,type) select id,'update' from inserted;
insert a(tid,type) select id,'update' from deleted;
endgo