StoredProcedure預存程序
CREATE PROCEDURE [dbo].[M_C_Page_Test]
@author varchar(50) = '莊英澔' -- 使用預設值參數，使用方式: EXEC [M_C_Page_Test] @author = 'XXX'
AS
BEGIN
Select title, summary, author, test_date,hit_no
from Test
where author like @author
END
Trigger觸發程序 - For | AFTER
CREATE TRIGGER M_C_Page_Test
ON Test
AFTER DELETE
AS
IF EXISTS (Select * From Test_Talk
where test_id = (select id from deleted))
BEGIN
raiserror('不可刪除已有留言之文章',1,1)
rollback rtun
END
GO
Trigger觸發程序 - INSTEAD OF
--IF條件檢查欲新增之文章標題是否已經存在，如果存在，就是更新資料；否則是插入新資料。
CREATE TRIGGER [dbo].[InsteadOf_Trigger]
ON [dbo].[Test]
INSTEAD OF Insert
AS
BEGIN
IF EXISTS (Select * from Test where title = (select title from inserted))
BEGIN
Update Test
set Test.summary = inserted.summary,
Test.article = inserted.article
From Test JOIN inserted
On Test.title = inserted.title
Print '更新一筆資料'
END
ELSE
BEGIN
Insert into Test(test_date,test_time,class,title ,summary
,article,author,hit_no,get_no,email_no,approved)
Select test_date,test_time,class,title,summary ,article
,author,hit_no,get_no,email_no,approved
From inserted
Print '新增一筆資料'
END
END
|
