MSSQLトリガ

---     



-- SQL Server                ，      ，         

 CREATE TABLE Student 

 ( 

    SID INT PRIMARY KEY       --   

 ) 



 CREATE TABLE BorrowRecord

    (

      --        

      BorrowRecord INT IDENTITY(1, 1) ,       --      

      StudentID INT ,                    --   

      BorrowDate DATETIME ,                --     

      ReturnDAte DATETIME,                --     

       

      

    )

--EXEC sp_rename 'student.sid', 'studentid', 'COLUMN'

INSERT BorrowRecord VALUES(1,GETDATE(),GETDATE())

INSERT BorrowRecord VALUES(1,GETDATE(),GETDATE())



INSERT BorrowRecord VALUES(2,GETDATE(),GETDATE())



INSERT BorrowRecord VALUES(3,GETDATE(),GETDATE())



------------Insert----------------------------

if (object_id('tr_insert', 'TR') is not null)    

drop trigger tr_insert

go

CREATE TRIGGER tr_insert

ON BorrowRecord

FOR insert

as

DECLARE @studentid INT;

SELECT @studentid=studentid FROM INSERTED

INSERT INTO student VALUES(@studentid) ;

go

INSERT BorrowRecord VALUES(11,GETDATE(),GETDATE())

----------------Update---------------------------

if (object_id('truStudent', 'TR') is not null)    

drop trigger truStudent

go

create TRIGGER truStudent 

    ON student -- Student        

    FOR UPDATE --       

AS

    IF UPDATE(studentid) 

        BEGIN

            UPDATE  BorrowRecord

            SET     StudentID = i.studentid

            FROM    BorrowRecord br ,

                    DELETED d ,

                    INSERTED i

            WHERE   br.StudentID = d.studentid



        END

--  

UPDATE student SET studentid=2 WHERE studentid=7

SELECT * FROM BorrowRecord

SELECT * FROM student

----------------delete---------------------------

if (object_id('deletri', 'TR') is not null)    

drop trigger deletri

go

CREATE TRIGGER deletri

ON student

FOR DELETE

AS 

DELETE BorrowRecord FROM BorrowRecord br,DELETED d

WHERE br.StudentID=d.studentid

--  

DELETE FROM student WHERE studentid=7

SELECT * FROM BorrowRecord