哪位高人能帮我实现以下功能:
现有两张表:
table1(id idType value)
table2(id name)
给表table2.id字段加一个约束,
要求table2.id的值必须从(select id from table1 where idType='1120')中取,
该如何实现啊!!
谢谢各位给点提示!!
现有两张表:
table1(id idType value)
table2(id name)
给表table2.id字段加一个约束,
要求table2.id的值必须从(select id from table1 where idType='1120')中取,
该如何实现啊!!
谢谢各位给点提示!!
否则的话,数据库的insert和delete将会相当的慢!
所以要实现这种约束只能在程序里控制!
on table2
as
if not exists(select A.id from table1 as A, inserted as I where idType='1120' and A.id=I.id)
begin
ROLLBACK TRANSACTION
end
on table2
for insert
as
if not exists(select A.id from table1 as A, inserted as I where idType='1120' and A.id=I.id)
begin
ROLLBACK TRANSACTION
end
union
select ..
on table2
for insert,update
as
delcare @f varchar(30)
select top 1 @f=id from inserted as i where not exists
(select * from table1 as A where idType='1120' and A.id=i.id)
if(@@rowcount>0)
raiserror('类型为1120的记录中没有刚刚插入的name为%s的记录,16,1,@f)
触发器中根本不用写 rollback transaction,只有存储过程中才需要写。触发器一旦发现异常,就会立刻自动回滚,并且会立刻退出。