ALTER TRIGGER [dbo].[tri_stu_aidu]
ON [dbo].[Student] AFTER INSERT,UPDATE,DELETE
AS
BEGIN
DECLARE @StuID char(10)
--INSERT操作
IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted)
BEGIN
SELECT @StuID=StuID FROM inserted
INSERT INTO [dbo].[Account] VALUES(@StuID,@StuID,0)
END
--DELETE操作
ELSE IF NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
DELETE FROM [dbo].[Account] WHERE [UserName]=(SELECT StuID FROM deleted)
--UPDATE操作
ELSE
UPDATE [dbo].[Account] SET [UserName]=(SELECT StuID FROM inserted)
WHERE [UserName]=(SELECT StuID FROM deleted)
END
这个触发器不支持Student表的批量更新和删除操作。这个触发器的用意是:每插入一个Student记录,都相应插入一个Account记录,使UserName和Password字段都等于[Student].[StuID]
Student表中的某条或某几条记录的StuID字段若更新,则更新Account表中对应的UserName字段。
若删除Student表中的一个记录,则删除对应的Account表中的记录。
ON [dbo].[Student]
AFTER INSERT,UPDATE,DELETE
AS
BEGIN
--INSERT操作
IF NOT EXISTS(SELECT * FROM deleted)
BEGIN
INSERT INTO [dbo].[Account]
SELECT StuID,StuID,0 FROM inserted
END
--DELETE操作
ELSE IF NOT EXISTS(SELECT * FROM inserted)
DELETE [dbo].[Account]
WHERE EXISTS (SELECT 1 FROM deleted WHERE [Account].[UserName]=StuID)
--UPDATE操作
ELSE
UPDATE A SET
A.[UserName]=C.StuID
FROM [dbo].[Account] A
JOIN (SELECT RN=ROW_NUMBER()OVER(ORDER BY GETDATE()),* FROM DELETED) B
ON A.[UserName]=B.StuID
JOIN (SELECT RN=ROW_NUMBER()OVER(ORDER BY GETDATE()),* FROM INSERTED) C
ON B.RN=C.RN
END
主要是Account表中还有其他的非Student的账号。
我用Account表不仅存储学生的账号,还存储了老师的账号。