--建立测试环境
Create table table0
(pid Int
)Create table table1
(Empid Int,
Num Int,
Joindate DateTime
)Create table table2
(Empid Int,
Joindate DateTime
)GO
--建立触发器
create TRIGGER [table1_Insert] ON dbo.table0
After INSERT
AS
insert into table1
select table0.pid,48,table2.joindate from table0
inner join table2
on table0.pid=table2.empid where table0.pid not in
(select Empid from table1) GO
--插入数据
Insert table2 Values(1,'2005-3-11')
Insert table2 Values(2,'2005-3-25')
Insert table2 Values(3,'2005-3-27')GO
--测试
Select * from table0
Select * from table1
Select * from table2Insert table0 Values(1)Select * from table0
Select * from table1
Select * from table2Insert table0 Values(2)
Insert table0 Values(3)Select * from table0
Select * from table1
Select * from table2
--删除测试环境
Drop table table0,table1,table2
--结果
/*
--第一次往table0中插入一条数据后,table1中数据
Empid Num Joindate
1 48 2005-03-11 00:00:00.000
--再次往table0中插入两条数据后,table1中数据
Empid Num Joindate
1 48 2005-03-11 00:00:00.000
2 48 2005-03-25 00:00:00.000
3 48 2005-03-27 00:00:00.000
*/
Create table table0
(pid Int
)Create table table1
(Empid Int,
Num Int,
Joindate DateTime
)Create table table2
(Empid Int,
Joindate DateTime
)GO
--建立触发器
create TRIGGER [table1_Insert] ON dbo.table0
After INSERT
AS
insert into table1
select table0.pid,48,table2.joindate from table0
inner join table2
on table0.pid=table2.empid where table0.pid not in
(select Empid from table1) GO
--插入数据
Insert table2 Values(1,'2005-3-11')
Insert table2 Values(2,'2005-3-25')
Insert table2 Values(3,'2005-3-27')GO
--测试
Select * from table0
Select * from table1
Select * from table2Insert table0 Values(1)Select * from table0
Select * from table1
Select * from table2Insert table0 Values(2)
Insert table0 Values(3)Select * from table0
Select * from table1
Select * from table2
--删除测试环境
Drop table table0,table1,table2
--结果
/*
--第一次往table0中插入一条数据后,table1中数据
Empid Num Joindate
1 48 2005-03-11 00:00:00.000
--再次往table0中插入两条数据后,table1中数据
Empid Num Joindate
1 48 2005-03-11 00:00:00.000
2 48 2005-03-25 00:00:00.000
3 48 2005-03-27 00:00:00.000
*/
才真正查入表中,这时你查询时表中当然没有记录了,这时才出现你说的延迟现象
问题2:你应该对inserted操作
insert into table1 select * from inserted
3、顺便说说你的触发器的效率,insert触发器不是用inserted表是危险的,数据量大的时候效率很难保证。
TO:
Yang_(扬帆破浪) ( )
你说得太对了!我原来忽略了这么大的一个问题!