表a(aid,aname)
表b(bid,bname,aname)
插入b数据时,怎么用触发器判断a表中有aname这个数据?create trigger trib
on b
for insert
as
if exists (select aname from a where aname=)这个后面我就不知道怎么搞定了
表b(bid,bname,aname)
插入b数据时,怎么用触发器判断a表中有aname这个数据?create trigger trib
on b
for insert
as
if exists (select aname from a where aname=)这个后面我就不知道怎么搞定了
create trigger trib on b instead of insert
as
insert into b select * from inserted t where exists(select 1 from a where aname=t.aname)
on b
for insert
as
if exists (select 1 from inserted as i join a on i.aname=a.aname)
...
insert into a select 1,'a'
go
create table b(id int,bname varchar(10),aname varchar(10))
go--创建触发器只插入aname在表a中存在的。。
create trigger trib on b instead of insert
as
insert into b select * from inserted t where exists(select 1 from a where aname=t.aname)
go
--插入测试
insert into b select 1,'sdfsad','a'
insert into b select 2,'xx','b'--查询
select * from b
go---
这句没看懂那个中间的t是什么意思啊?翻译一下:向表b中添加数据,添加的数据是:
select * from inserted--(一个系统的保存临时插入数据表)
t?
where exists(select 1 from a where aname=t.aname)存在的1行当aname=t.aname时...t是不是inserted的别名啊?
on b
for insert
as
declare @s nvarchar(100)
select @s=aname from inserted
if not exists (select aname from a where aname=@s)
delete from a where aname=@s哈哈,搞定了
on b
for insert
as
declare @s nvarchar(100)
select @s=aname from inserted
if not exists (select aname from a where aname=@s)
delete from a where aname=@s哈哈,搞定了
我是这样认为的
呵呵
那和for insert有什么区别呢?还是不是懂...
哦!我明白了!可是我写的那个有错吗?为什么没有执行?不解!继续再看看
不是哦!不好意思哦!
create trigger trib
on b
for insert
as
if exists (select aname from a,inserted b where a.aname=b.aname)
这个不行哦,注意楼上讲的for和instead of的区别。
用这个会插入三条记录,
insert into b select 1,'sdfsad','a'
insert into b select 1,'sdfsad','a'
insert into b select 2,'xx','b' 正好验证了 楼上讲的for和instead of的区别