Question

sql server 2005 update 触发器

A表：主键自动增长列AID，money；
B表：主键自动增长列BID，money
求:
A表的money增加和和减少，b表的money 插入增加或减少的金钱额

Answer 1

CREATE TABLE A(
AID INT IDENTITY(1, 1) PRIMARY KEY,
money INT
);

CREATE TABLE B(
BID INT IDENTITY(1, 1) PRIMARY KEY,
money INT
);
go

CREATE TRIGGER AfterUpdateA
ON A
FOR INSERT,UPDATE,DELETE
AS
BEGIN

IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
BEGIN
-- 插入触发.
INSERT INTO B
SELECT
inserted.money
FROM
inserted;
END;

IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
BEGIN
-- 更新触发.
INSERT INTO B
SELECT
inserted.money - deleted.money
FROM
inserted, deleted
WHERE
inserted.AID = deleted.AID;
END;

IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
BEGIN
-- 删除触发
INSERT INTO B
SELECT
0 - deleted.money
FROM
deleted;
END;
END;
go

-- 一条SQL语句，插入一条数据
INSERT INTO A VALUES (100);

-- 一条SQL语句，插入多条数据
INSERT INTO A
SELECT 10
UNION ALL SELECT 20;
GO

-- 一条SQL语句，更新一条数据
UPDATE A SET money = money - 50 WHERE AID = 1;

-- 一条SQL语句，更新多条数据
UPDATE A SET money = money + 50 WHERE AID != 1;
GO

-- 一条SQL语句，删除一条数据
DELETE FROM A WHERE AID = 1;

-- 一条SQL语句，删除多条数据
DELETE FROM A WHERE AID != 1;
GO

1> SELECT * FROM B
2> GO
BID money
----------- -----------
1 100
2 20
3 10
4 -50
5 50
6 50
7 -50
8 -70
9 -60

(9 行受影响)

Answer 2

CREATE TRIGGER [B] ON [dbo].[A]
FOR update
AS
begin
insert B as B set (select money as A from A where A.AID = inserted) values (B.money)
end