CREATE TRIGGER T_Insert ON a1
FOR INSERT
AS
BEGIN
INSERT INTO a2
SELECT * FROM INSERTED A LEFT JOIN a2 B ON B.name = A.name
WHERE B.name IS NULL UPDATE A
SET A.shuliang2 = ISNULL(A.shuliang2,0) - B.shuliang
FROM a2 A INNER JOIN INSERTED B ON B.name = A.name
END
FOR INSERT
AS
BEGIN
INSERT INTO a2
SELECT * FROM INSERTED A LEFT JOIN a2 B ON B.name = A.name
WHERE B.name IS NULL UPDATE A
SET A.shuliang2 = ISNULL(A.shuliang2,0) - B.shuliang
FROM a2 A INNER JOIN INSERTED B ON B.name = A.name
END
思路是先插入没有的,然后一次更新已有的,刚新插入a2表的
CREATE TRIGGER T_Insert ON a1
FOR INSERT
AS
BEGIN
INSERT INTO a2(name,shuliang2,……)
SELECT A.name,0,…… FROM INSERTED A LEFT JOIN a2 B ON B.name = A.name
WHERE B.name IS NULL UPDATE A
SET A.shuliang2 = ISNULL(A.shuliang2,0) - B.shuliang
FROM a2 A INNER JOIN INSERTED B ON B.name = A.name
END
FOR INSERT
AS
BEGIN
UPDATE A2 SET shuliang2=shuliang2-b.shuliang FROM INSERTED b where b.name = A2.name2
INSERT a2 SELECT * FROM INSERTED where id not in (select id2 from a2)
END
FOR INSERT
AS
BEGIN
INSERT INTO a2
SELECT * FROM INSERTED A LEFT JOIN a2 B ON B.name = A.name
WHERE B.name IS NULL UPDATE a2
SET shuliang2 = ISNULL(shuliang2,0) - ISNULL(B.shuliang, 0)
FROM INSERTED B
WHERE B.name = a2.name
END
FOR INSERT
AS
BEGIN
IF EXISTS (SELECT 1 FROM A2, INSERTED B,WHERE A2.NAME=B.NAME)
UPDATE A2 SET SHULIANG2=SHULIANG2-B.SHULIANG FROM INSERTED B WHERE B.NAME=A2.NAME2
ELSE
INSERT INTO a2(name,shuliang2,……)
SELECT A.name,0,…… FROM INSERTED A LEFT JOIN a2 B ON B.name = A.nameEND
GO
FOR INSERT
AS
BEGIN
UPDATE A2 SET shuliang2=shuliang2-b.shuliang FROM INSERTED b where b.name = A2.name2
INSERT a2 SELECT * FROM INSERTED where id not in (select id2 from a2)
END