我刚开始学触发器,遇到了问题,不知道怎么解决,能帮我一下吗,
实现的功能是:当我在 tb_action_detail1 中插入一条记录,而tb_action_updated更新,那条记录,但是插入了,就是不能把tb_action_updated中的那条记录更新。
插入到tb_action_detail1中的数据是在tb_action_updated中是有的,只是时间不同,我想把那条更新掉
能帮我看看是什么问题吗
CREATE TRIGGER t1
ON tb_action_detail1 FOR UPDATE,INSERT
AS BEGIN TRUNCATE
TABLE tb_action_updated INSERT tb_action_detail1
SELECT a.custome_id,a.created_id,a.created_Date,a.descriptiona
FROM tb_action_detail1 a INNER JOIN
(SELECT MAX(created_date) as mt,custome_id,created_id ,descriptiona
FROM tb_action_updated b GROUP BY custome_id,created_id ,descriptiona) b
ON a.custome_id=b.custome_id
AND a.created_id=b.created_id
AND a.created_date=b.mt
and a.descriptiona=b.descriptiona
END
实现的功能是:当我在 tb_action_detail1 中插入一条记录,而tb_action_updated更新,那条记录,但是插入了,就是不能把tb_action_updated中的那条记录更新。
插入到tb_action_detail1中的数据是在tb_action_updated中是有的,只是时间不同,我想把那条更新掉
能帮我看看是什么问题吗
CREATE TRIGGER t1
ON tb_action_detail1 FOR UPDATE,INSERT
AS BEGIN TRUNCATE
TABLE tb_action_updated INSERT tb_action_detail1
SELECT a.custome_id,a.created_id,a.created_Date,a.descriptiona
FROM tb_action_detail1 a INNER JOIN
(SELECT MAX(created_date) as mt,custome_id,created_id ,descriptiona
FROM tb_action_updated b GROUP BY custome_id,created_id ,descriptiona) b
ON a.custome_id=b.custome_id
AND a.created_id=b.created_id
AND a.created_date=b.mt
and a.descriptiona=b.descriptiona
END
create table t1(id int)
create table t2(id1 int)
insert into t2 select 1
go
create trigger tr1 on t1 for update,insert
as
begin
update t2 set id1=2 from t2 inner join inserted t3 on t2.id1=t3.id
end
go
select * from t1
select * from t2
insert into t1 select 1
select * from t2
go
drop table t1
drop table t2
/*
id
-----------id1
-----------
1id1
-----------
2
*/
看看这个
after insert
as
if exists(select admin from TB_AD where admin in (select admin from inserted))
update
TB_AD set password
=(select password from tb_admin where admin
= (select admin from inserted)
and
id=(select id from inserted)
),
nzm=(select nzm from tb_admin where admin
= (select admin from inserted)
and
id=(select id from inserted)
group by admin,password,nzm
)
where admin= (select admin from inserted) and id=(select id from inserted) and nzm=(select nzm from inserted)
else
insert into TB_AD
select (select admin from inserted),(select password from inserted),(select nzm from tb_admin where admin= (select admin from inserted) group by admin,password,nzm) 这个怎么不能帮TB_aD中的数据更新了,哪位帮我看一下谢谢
如数据:TB_ADMIN表中 id,admin,password,nzm
1 pp in ok
A
2 pp zhang ui
tb_ad 表中 id admin,password,nzm
1 pp in ok
y 要把TB_AD表中的第一列更新为TB_ADMIN中的第二列