1: 设置主键 前更新方式设为依关键字 后台的触发器中不要更新自身数据 如果还不行将Refresh换成Close + Open + Locate2: Create Trigger tri_srctable_del on SrcTable For Delete AS Delete destTable from deleted d,destTable b where d.BookID=b.BookID
为什么做那么多触发器,在没有把握之前,最好少用触发器。 CREATE TRIGGER [TRIGGER NAME] ON [table1] FOR DELETE AS begin delete from table2 where bookname in (select bookname from deleted) end
1. /* 当插入或者修改数据后,将数据插入到表Allbook */ Create trigger TIUA_Publishbook ON Publishbook after insert,update ASDECLARE @Count int SELECT @Count = COUNT(*) FROM INSERTED -- Check if any rows were deleted. If so, delete from the dependent table IF @Count > 0 Begin Delete Allbook End Begin Insert into "Allbook"("BookID","Book_Name","Worksheet","Send_Com","Send_Need","End_Date") Select P.BookID,P.Book_Name,P.Worksheet,P.Send_Com,P.Send_Amount,P.End_Date From Publishbook P -- Where P.Book_Name=Allbook.Book_Name AND P.Send_Com=Allbook.Send_Com
End -- If anything went wrong, roll the transaction back IF @@ERROR != 0 ROLLBACK TRAN GO2.怎么设置计数字段呢,:),谢谢了,我在线等你呢
Create trigger TIUA_Publishbook ON Publishbook for insert,update,delete AS delete from Allbook where BookID in (select BookID from INSERTED )Insert into Allbook(BookID,Book_Name,Worksheet,Send_Com,Send_Need,End_Date) Select distinct P.BookID,P.Book_Name,P.Worksheet,P.Send_Com,P.Send_Amount,P.End_Date From Publishbook P WHERE p.BookID in (select BookID from INSERTED )GO
下面是一个建表脚本,其中的fc就是计算字段create table ta(fa int default 0 ,fb int default 0 ,fc as (fa-fb) )
Create trigger TIUA_Publishbook ON Publishbook for insert,update,delete AS delete from Allbook where BookID in (select BookID from Deleted) --这里需要改一下Insert into Allbook(BookID,Book_Name,Worksheet,Send_Com,Send_Need,End_Date) Select distinct P.BookID,P.Book_Name,P.Worksheet,P.Send_Com,P.Send_Amount,P.End_Date From Publishbook P WHERE p.BookID in (select BookID from INSERTED )GO
Create Trigger tri_srctable_del on SrcTable
For Delete
AS
Delete destTable from deleted d,destTable b where d.BookID=b.BookID
CREATE TRIGGER [TRIGGER NAME] ON [table1]
FOR DELETE
AS
begin
delete from table2 where bookname in (select bookname from deleted)
end
用query进行数据更新的前提是你的SQL语句不能出现 distinct,group by,order by,子查询等等,把把query1的requestlive属性设为true了吗?
1.当我在a表插入或者修改数据后,把相应的数据插入到b表2.当在a表插入或者修改数据后,a表中的2列减去1列,得到的数据插入3列。
1)最简单的方法,在触发器的前面加上 return语句,就可以了
2)使用以下脚本
禁用: alter table 表名 disable trigger 触发器名
启用: alter table 表名 enable trigger 触发器名我喜欢用第一种
1.当我在a表插入或者修改数据后,把相应的数据插入到b表
ps:不管是插入还是修改,对b表,你都现删除再插入,而不要对b修改2.当在a表插入或者修改数据后,a表中的2列减去1列,得到的数据插入3列。
ps:这个你在a表建了主键应该不会有问题,你最好更新的时候用你的编号字段定位比较好。
谢谢了,但是对你说的触发器写法有点不懂,
1.where语句应该怎么写呢?
2.where应该怎么定位?TO:qizhanfeng(glacier)
请原谅我刚学呀,我不懂你说的呢。
/* 当插入或者修改数据后,将数据插入到表Allbook */
Create trigger TIUA_Publishbook ON Publishbook
after insert,update
ASDECLARE @Count int
SELECT @Count = COUNT(*) FROM INSERTED -- Check if any rows were deleted. If so, delete from the dependent table
IF @Count > 0 Begin Delete Allbook
End Begin
Insert into "Allbook"("BookID","Book_Name","Worksheet","Send_Com","Send_Need","End_Date")
Select P.BookID,P.Book_Name,P.Worksheet,P.Send_Com,P.Send_Amount,P.End_Date
From Publishbook P
-- Where P.Book_Name=Allbook.Book_Name AND P.Send_Com=Allbook.Send_Com
End -- If anything went wrong, roll the transaction back
IF @@ERROR != 0
ROLLBACK TRAN
GO2.怎么设置计数字段呢,:),谢谢了,我在线等你呢
0:怎么关闭呢?:))
1)最简单的方法,在触发器的前面加上 return语句,就可以了
2)使用以下脚本
禁用: alter table 表名 disable trigger 触发器名
启用: alter table 表名 enable trigger 触发器名我喜欢用第一种
1.当我在a表插入或者修改数据后,把相应的数据插入到b表
ps:不管是插入还是修改,对b表,你都现删除再插入,而不要对b修改2.当在a表插入或者修改数据后,a表中的2列减去1列,得到的数据插入3列。
ps:这个你在a表建了主键应该不会有问题,你最好更新的时候用你的编号字段定位比较好。
for insert,update,delete
AS
delete from Allbook
where BookID in (select BookID from INSERTED )Insert into Allbook(BookID,Book_Name,Worksheet,Send_Com,Send_Need,End_Date)
Select distinct P.BookID,P.Book_Name,P.Worksheet,P.Send_Com,P.Send_Amount,P.End_Date
From Publishbook P
WHERE p.BookID in (select BookID from INSERTED )GO
例如:表结构如下
create table ta(fa int default 0 ,fb int default 0 ,fc as (fa-fb) )
1:当执行
insert ta(fa,fb) values(2,3)
这时:Deleted为空记录,inserted 为 (2,3)
2:当执行
update ta set fa=4 where fa=2
这时:Deleted (2,3),inserted 为 (4,3)
3:当执行
delete ta where fa=4
这时:Deleted (4,3),inserted 为空记录所以怎么会有Updated阿,而是,不论你是增加,修改,删除,触发器中都会存在Deleted,inserted两个数据集,只有时它是空记录。
可以修改,修改后的数据可以插入到Allbook;
可删除,但是只对PublishBook有效,对Allbook无效,还出现错误信息:
无法为更新定位行。一些值可能已在最后一次读取后已更改。
可增加,数据对两个表都有效,但是会出现错误信息:
无法为更新定位行。一些值可能已在最后一次读取后已更改。
for insert,update,delete
AS
delete from Allbook
where BookID in (select BookID from Deleted) --这里需要改一下Insert into Allbook(BookID,Book_Name,Worksheet,Send_Com,Send_Need,End_Date)
Select distinct P.BookID,P.Book_Name,P.Worksheet,P.Send_Com,P.Send_Amount,P.End_Date
From Publishbook P
WHERE p.BookID in (select BookID from INSERTED )GO
如果还保错,那你就要在修改表Publishbook 的时候,关闭表Allbook ,等修改完之后再打开Allbook 表。
如果还保错,那你就要在修改表Publishbook 的时候,关闭表Allbook ,等修改完之后再打开Allbook 表。
-----工作了!
-----那肯定在程序代码中有置它为真的语句了。
--要不你将Allbook表除了书号以外你再加给自增的编号字段.