有两个表A,B,结构如下:
A表:
a1,a2,a3,a4,a5,a6,c
B表:(b2字段表示日期)
b1,b2,b3,c现需要在A表中建立一个触发器,当A表中有新增记录时,用B表中的b3(要求b2为最新的一条记录,且要求A表和B表中的C字段相同的条件下才进行)替换A表中的a3(仅针对A表的新增记录);请问如何实现?
A表:
a1,a2,a3,a4,a5,a6,c
B表:(b2字段表示日期)
b1,b2,b3,c现需要在A表中建立一个触发器,当A表中有新增记录时,用B表中的b3(要求b2为最新的一条记录,且要求A表和B表中的C字段相同的条件下才进行)替换A表中的a3(仅针对A表的新增记录);请问如何实现?
解决方案 »
- 奇怪,print和Order BY的bug?
- 有道SQL题请求帮解释(在线等待...)
- 在C#下,一条SQL查询语句问题 谢了
- 求一条SQL语句
- 将sql2008 中的时间转成mysql dede 中的时间整型?
- [SQLServer2008R2]select * From (select * from table) 嵌套查询问题
- 急急,一段sql语句有问题,请高手赐教!
- SQL Server连接字串和端口的问题。。。
- 如何在SQL SERVER中存储各种文件(如图象,MP3,WORD)
- 这个问题你会吗?
- KCSW(库存事务)触发器怎么改?
- 为什么ODBC中cstring对象一定要用FORMAT方法,不明白
on ta
instead of insert
as
begin
insert into ta
select a1,a2,b3,a4,a5,a6,i.c
from inserted i
left join tb b
on i.c = b.c
where not exists(select 1 from tb where b1 = b.b1 and b3 = b.b3 and c = b.c and b2 > b.b2)
end
go
FOR INSERT
AS
BEGIN
UPDATE A SET A3=T.B3 FROM INSERTED I JOIN
(SELECT * FROM B T WHERE NOT EXISTS(SELECT 1 FROM B WHERE B3=T.B3 AND T.B2<B2)AS T
ON I.C=T.C
END
CREATE TRIGGER TRIA ON paymoney_rec
FOR INSERT
AS
BEGIN
UPDATE paymoney_rec SET price_j=T.frz_z FROM INSERTED I JOIN
(SELECT * FROM freezedata T WHERE NOT EXISTS(SELECT 1 FROM freezedata WHERE frz_z=T.frz_z AND T.m_date<m_date)AS T
ON I.meter_id=T.meter_id
END
f_month meter_id m_date frz_z5 910137 2009-6-21 8:44:00 16.2
5 914821 2009-6-21 8:44:00 37.08
5 914854 2009-6-21 8:44:00 0.02
5 914861 2009-6-21 8:44:00 0.02A表新增的数据user_id meter_id price_j m_id
2609000053374 914821 0.4710 2要达到的目的就是把A表中新增记录的price_j替换为frz_z,要求B表中m_date为最新的一条数据,且两表中meter_id值相等;
f_month meter_id m_date frz_z5 910137 2009-6-21 8:44:00 16.2
5 914821 2009-6-21 8:44:00 37.08
5 914854 2009-6-21 8:44:00 0.02
5 914861 2009-6-21 8:44:00 0.02A表新增的数据user_id meter_id price_j m_id
2609000053374 914821 0.4710 2要达到的目的就是把A表中新增记录的price_j替换为frz_z,要求B表中m_date为最新的一条数据,且两表中meter_id值相等;
B表数据:
f_month meter_id m_date frz_z5 910137 2009-6-21 8:44:00 16.2
5 914821 2009-6-21 8:44:00 37.08
5 914854 2009-6-21 8:44:00 0.02
5 914861 2009-6-21 8:44:00 0.02A表新增的数据user_id meter_id price_j m_id
2609000053374 914821 0.4710 2要达到的目的就是把A表中新增记录的price_j替换为frz_z,要求B表中m_date为最新的一条数据,且两表中meter_id值相等;
FOR INSERTED
AS
BEGIN
UPDATE A SET A. price_j=frz_z FROM INSERTED I
JOIN
(SELECT * FROM (SELECT B.* FROM B LEFT JOIN INSERTED AS INS ON B.meter_id=INS.meter_id )AS T
WHERE NOT EXISTS
(SELECT 1 FROM (SELECT B.* FROM B LEFT JOIN INSERTED AS INS ON B.meter_id=INS.meter_id) AS T1 WHERE T.f_month=T1.f_month AND T.meter_id=T1.meter_id AND T. m_date<T1. m_date ))AS T2ON I.meter_id=T2.meter_id
END
试试???
INSERT B
SELECT 5 , 910137 , '2009-6-21 8:44:00' , 16.2 union all
SELECT 5 , 914821 , '2009-6-21 8:44:00' , 37.08 union all
SELECT 5 , 914854 , '2009-6-21 8:44:00' , 0.02 union all
SELECT 5 , 914861 , '2009-6-21 8:44:00' , 0.02 --DROP TABLE BSELECT * FROM A
SELECT * FROM BCREATE TRIGGER TRIA ON A
FOR INSERT
AS
BEGIN
UPDATE A SET A. price_j=frz_z FROM INSERTED I
JOIN
(SELECT * FROM (SELECT B.* FROM B LEFT JOIN INSERTED AS INS ON B.meter_id=INS.meter_id )AS T
WHERE NOT EXISTS
(SELECT 1 FROM (SELECT B.* FROM B LEFT JOIN INSERTED AS INS ON B.meter_id=INS.meter_id) AS T1 WHERE T.f_month=T1.f_month AND T.meter_id=T1.meter_id AND T. m_date<T1. m_date ))AS T2ON I.meter_id=T2.meter_id
ENDINSERT A SELECT '2609000053374', 914821 , 0.4710 , 2SELECT * FROM A
user_id meter_id price_j m_id
-------------------- ----------- --------------------- -----------
2609000053374 914821 37.0800 2(所影响的行数为 1 行)
FOR INSERT
AS
BEGIN
UPDATE A SET A. price_j=frz_z FROM INSERTED I
JOIN
(SELECT * FROM (SELECT B.* FROM B LEFT JOIN INSERTED AS INS ON B.meter_id=INS.meter_id )AS T
WHERE NOT EXISTS
(SELECT 1 FROM (SELECT B.* FROM B LEFT JOIN INSERTED AS INS ON B.meter_id=INS.meter_id) AS T1 WHERE T.f_month=T1.f_month AND T.meter_id=T1.meter_id AND T. m_date<T1. m_date ))AS T2ON I.meter_id=T2.meter_id
--add*********************************
where i.主键=A的主键
--************************************
END
加个条件
CREATE TRIGGER TRIA ON A
FOR INSERT
AS
BEGIN
UPDATE A SET A. price_j=frz_z FROM INSERTED I
JOIN
(SELECT * FROM (SELECT B.* FROM B LEFT JOIN INSERTED AS INS ON B.meter_id=INS.meter_id )AS T
WHERE NOT EXISTS
(SELECT 1 FROM (SELECT B.* FROM B LEFT JOIN INSERTED AS INS ON B.meter_id=INS.meter_id) AS T1 WHERE T.f_month=T1.f_month AND T.meter_id=T1.meter_id AND T. m_date<T1. m_date ))AS T2ON I.meter_id=T2.meter_id WHERE A.user_id IN (SELECT user_id FROM INSERTED)
END