34,838
社区成员




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
ALTER TRIGGER [dbo].[tri_stu_aidu]
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