A表包含3個字段
A(DH,PH,TM)
DH,PH是主關鍵字,(A表中PH,TM有可能存在重復值)B表包含2個字段
B(PH,TM) PH,TM是主關鍵字 我想實現以下功能:
A表中插入或刪除記錄時,自動將PH,TM插入到B表中或從B表中刪除.
即B表中包含A表中所有的PH,TM值.觸發器怎麼寫比較好呢?
A(DH,PH,TM)
DH,PH是主關鍵字,(A表中PH,TM有可能存在重復值)B表包含2個字段
B(PH,TM) PH,TM是主關鍵字 我想實現以下功能:
A表中插入或刪除記錄時,自動將PH,TM插入到B表中或從B表中刪除.
即B表中包含A表中所有的PH,TM值.觸發器怎麼寫比較好呢?
A(DH,PH,TM)
DH,PH是主关键字,(A表中PH,TM有可能存在重复值) B表包含2个字段
B(PH,TM) PH,TM是主关键字 我想实现以下功能:
A表中插入或删除记录时,自动将PH,TM插入到B表中或从B表中删除.
即B表中包含A表中所有的PH,TM值. 触发器怎么写比较好呢?
create trigger tri_taskname1
on taskname1
for insert,update,delete
as
if not exists(select 1 from inserted) --delete
begin
delete taskname2 from deleted where taskname2.id=deleted.id
end
if not exists(select 1 from deleted) --insert
begin
insert taskname2 select * from inserted
end
else --update
begin
update t2
set t2.name=i.name
from taskname2 t2,inserted i
where t2.id=i.id
end
for insert,update,delete
as
begin
delete from b where exists(select 1 from deteled where ph=b.ph and tm=b.tm)
insert into b select distinct ph,tm from inserted a where not exists(
select 1 from b where ph=a.ph and tm=a.tm
)
end
for insert,delete
as
begin
insert into B
select PH,TM
from inserted i
where not exists(select * from B where i.TM=tm)
delete B
from deleted d
where d.TM=b.tm
end
for insert,delete
as
if exists(select 1 from inserted)
and not exists(select 1 from deleted)
begin
insert b select ph,tm from inserted
end
if exists(select 1 from deleted)
and not exists(select 1 from inserted)
begin
delete b where exists(select 1 from deleted where ph=b.ph and tm=b.pm)
end
go
CREATE TABLE [A] (
DH [varchar] (10) NOT NULL ,
PH [varchar] (40) NOT NULL ,
TM [varchar] (40) ,
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[DH],
[PH]
) ON [PRIMARY]
) ON [PRIMARY]
GOCREATE TABLE [B] (
PH [varchar] (40) NOT NULL ,
TM [varchar] (40) NOT NULL ,
CONSTRAINT [PK_B] PRIMARY KEY CLUSTERED
(
[PH],
[TM]
) ON [PRIMARY]
) ON [PRIMARY]
GO
大家幫我寫具體點.
insert into A(DH,PH,TM)
select 'D1','P1','P11'
union all
select 'D2','P1','P11'
union all
select 'D3','P1','P12'
union all
select 'D2','P2','P2'insert into B(PH,TM)
select 'P1','P11'
union all
select 'P1','P12'
union all
select 'P2','P2'
select * from A
select * from B
select * from B
insert into A(DH,PH,TM)
select 'D4','P1','P11'
select * from B#6楼 的通不過,
出現錯誤:
Server: Msg 2627, Level 14, State 1, Procedure tg, Line 7
Violation of PRIMARY KEY constraint 'PK_B'. Cannot insert duplicate key in object 'B'.
The statement has been terminated.
難道一定要用存儲過程來實現了?
试过5楼么5樓的會刪除a表中還有的記錄."請注意A表中的PH,TM的值可能會重復,並且可能會批次刪除! "
A表中插入或刪除記錄時,自動將PH,TM插入到B表中或從B表中刪除.
即B表中包含A表中所有的PH,TM值. 如果本来没有记录,先后插入A
1 1 1
2 2 2
3 1 1
4 2 2
后删除记录
3 1 1
最后B表留什么记录
再修改
4 2 2
为
4 3 3
呢?
楼主请回答
删除的是B表n内容阿阿
create trigger k on A
for insert,delete
as
begin
insert into B
select PH,TM
from inserted i
where not exists(select * from B where i.TM=tm)
delete B
from deleted d
where d.TM=b.tm and not exists(select * from A where PH<>d.ph)
end
再去试试
即B表中包含A表中所有的PH,TM值.
这一句话那这个建议不要用触发器来做,因为你每次删除或者修改A表,触发器很可能需要扫瞄整个A表才能得出是否需要删除的结论,性能代价太大,不合算,不如查询时select PH,TM
from a
group by PH,TM
或者设置成视图
如果不考虑性能create trigger k on A
for insert,delete
as
begin
insert into B
select DISTINCT PH,TM
from inserted i
where not exists(select * from B where PH=i.ph and TM=i.tm)delete B
from deleted d
where d.TM=b.tm
and d.PH=b.ph
and not exists(select * from A
where PH=d.ph
and TM=d.tm )
end
但是需不需要加上update呢?create trigger K on a
for insert,delete,update
as
begin
insert into B
select DISTINCT PH,TM
from inserted i
where not exists(select * from B where PH=i.ph and TM=i.tm)
delete B
from deleted d
where d.TM=b.tm
and d.PH=b.ph
and not exists(select * from A where PH=d.ph and TM=d.tm )
end
create trigger trigID_A on A
for insert,delete
as
if exists(select * from inserted) --插入时
begin
insert into B (ph,tm) select ph,tm from inserted where not exists(select * from b where ph=inserted.ph and tm=inserted.tm)
end else --删除时
begin
delete B where exists(select * from deleted where ph=b.ph and tm=b.tm)
end
create trigger trigID_A on A
for insert,delete
as
if exists(select * from inserted) --插入时
begin
insert into B (ph,tm) select distinct ph,tm from inserted where not exists(select * from b where ph=inserted.ph and tm=inserted.tm)
end else --删除时
begin
delete B where exists(select * from deleted where ph=b.ph and tm=b.tm)
end
(小陈) 大哥,你的刪除條件是不符合我的要求的.你都沒有比較a表,要a表中全部不存在時才能刪除b表中的PH,TM.只要a表中還存在PH,TM,b表都不能刪除,除非a表中沒有相同的PH,TM存在時才能刪除b表中的記錄.