-- 按照註釋一步步執行 看效果

--建立表

IF OBJECT_ID("tstudent") > 0

DROP TABLE tstudent

GO

CREATE TABLE tstudent

(

fstudentno VARCHAR(10) PRIMARY KEY ,

fname NVARCHAR(10),

)

IF OBJECT_ID("tScore") > 0

DROP TABLE tScore

CREATE TABLE tScore

(

fstudentno VARCHAR(10) PRIMARY KEY ,

fscroe INT

)

GO

--插入測試資料

INSERT dbo.tstudent

( fstudentno, fname )

VALUES ( "001", -- fstudentno - varchar(10)

N"小張" -- fname - nvarchar(10)

)

INSERT dbo.tScore

( fstudentno, fscroe )

VALUES ( "001", -- fstudentno - varchar(10)

90 -- fscroe - int

)

--檢視插入效果

SELECT *

FROM dbo.tScore

SELECT *

FROM dbo.tstudent

--建立觸發器

if (object_id("tgr_DeleteByStudentNo", "tr") is not null)

drop trigger tgr_DeleteByStudentNo

Go

CREATE trigger tgr_DeleteByStudentNo ON tstudent

AFTER DELETE

as

DECLARE @strStudentNo VARCHAR(10)

SELECT @strStudentNo = fstudentno FROM deleted

DELETE FROM tScore WHERE fstudentno = @strStudentNo

DELETE FROM tstudent WHERE fstudentno = "001"

SELECT *

FROM dbo.tScore

SELECT *

FROM dbo.tstudent