--第一步:找出test2表上的外键约束名字
--2000
exec sp_helpconstraint 'test2'
--可以在constraint_name 属性中找到外键约束名字
---------------------------------
FK__test2__id__08EA5793*/
--第二步:删除外键约束
alter table test2 drop constraint FK__test2__id__08EA5793
--第三步:检查表上是否还有外键约束
--只要使用第一步里面的查找语句即可
alter table user_role add constraint fk_user_role_user
foreign key(u_idName) references [user](u_idName)
alter table [Vise_WorkBack] add constraint fk_Vise_WorkBack_Vise_WorkFlow
foreign key(wb_FileNO) references Vise_WorkFlow(w_FileNO)
--刪除約束
alter table Vise_WorkFlow drop constraint fk_Vise_WorkFlow_Vise_WorkBack
--創建觸發器
alter TRIGGER Vise_WorkFlow_update on Vise_WorkFlow FOR update
AS
if update(w_no)
BEGIN
update Vise_WorkFlow SET Vise_WorkFlow.w_isOk=1 from Vise_WorkFlow,inserted
where Vise_WorkFlow.w_fileNo=inserted.w_fileNo and inserted.w_no=2
if @@error<>0 rollback transaction
END
GO
alter TRIGGER Vise_WorkFlow_update on Vise_WorkFlow FOR update
AS
if update(w_no)
BEGIN
IF EXISTS(SELECT * FROM INSERTED,Vise_WorkFlow WHERE Vise_WorkFlow.w_fileNo=inserted.w_fileNo and INSERTED.W_NO=2 AND LEN(Vise_WorkFlow.w_conductorID)=0)
begin
update Vise_WorkFlow SET Vise_WorkFlow.w_isOk=1 from Vise_WorkFlow,inserted
where Vise_WorkFlow.w_fileNo=inserted.w_fileNo and inserted.w_no=2
end
else
begin
update Vise_WorkFlow SET Vise_WorkFlow.w_isOk=1 from Vise_WorkFlow,inserted
where Vise_WorkFlow.w_fileNo=inserted.w_fileNo and inserted.w_no=3 AND LEN(Vise_WorkFlow.w_conductorID)>0
end
if @@error<>0 rollback transaction
END
GO