Question

在SqlServer中定义触发器

两个表，person（pno,pname,birthday）和salary(pno,fact,month)，有相同的字段pno，定义一个触发器，当修改或删除person表中的记录时，同时修改或删除salary表中的相关字段。
要写在一个触发器里的！！！

Answer 1

最简单的办法，这两个表之间建立一个主外键关系，级联更新、删除，这样效率最高也最简单。

非要写触发器的话，如下：
CREATE TRIGGER tr_person_ud
ON person
AFTER DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;

--判断是删除还是修改
DECLARE @IsDel bit
SET @IsDel = 1
IF(EXISTS(SELECT * FROM inserted)) SET @IsDel = 0

IF(@IsDel = 1)
BEGIN
DELETE FROM salary WHERE pno IN(SELECT pno FROM deleted)
END
ELSE
BEGIN
--改变后的值
DECLARE @newTable TABLE(autoId int IDENTITY(1,1),pno int NOT NULL)
INSERT INTO @newTable(pno)
SELECT pno FROM inserted
--改变前的值
DECLARE @oldTable TABLE(autoId int IDENTITY(1,1),pno int NOT NULL)
INSERT INTO @oldTable(pno)
SELECT pno FROM deleted
--循环修改
DECLARE @maxId int,@loopId int,@oldpno int,@newpno int
SELECT @maxId = max(autoId),@loopId = 1 FROM @newTable
WHILE(@loopId <= @maxId)
BEGIN
SELECT @oldpno = pno FROM @oldTable WHERE autoId = @loopId
SELECT @newpno = pno FROM @newTable WHERE autoId = @loopId
UPDATE salary SET pno = @newpno WHERE pno = @oldpno
SET @loopId = @loopId + 1
END
END

SET NOCOUNT OFF;
END

Answer 2

CREATE TRIGGER MyTr1 ON person
FOR UPDATE
AS
IF UPDATE(pno)
BEGIN
UPDATE salary
SET pno=自己定义
FROM salary
WHERE pno=(SELECT pno FROM Inserted,Deleted WHERE Inserted.pno=Deleted.pno)
END

GO
以上是有关修改的触发器，关于删除的你可以按同样的方法写。

CREATE TRIGGER MyTr2 ON person
FOR DELETE
AS
BEGIN
DELETE
FROM salary
WHERE salary.pno=(SELECT pno FROM Deleted)
END

GO