Database Trigger (Insert, Delete)

28 四月, 2009 at 12:26 下午 發表留言

而家好努力咁寫緊database schema 同erd ~~嗚…吐血~~

上司suggest 同trigger, 好彩有佢提一提, 唔係今次都唔記得呢野樣~~

trigger 都唔係太難, 我而家識d basic 野~~

首先你要開一個新table 係for recording trigger 時的資料, 我就copy 番要trigger 既table 再加d fields入去, 揀你想trigger 既table, 再揀new 一個trigger~~


dbtrigger

Original Code when you create the Trigger in the database management studio:

— ================================================
— Template generated from Template Explorer using:
— Create Trigger (New Menu).SQL

— Use the Specify Values for Template Parameters
— command (Ctrl-Shift-M) to fill in the parameter
— values below.

— See additional Create Trigger templates for more
— examples of different Trigger statements.

— This block of comments will not be included in
— the definition of the function.
— ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:        <Author,,Name>
— Create date: <Create Date,,>
— Description:    <Description,,>
— =============================================
CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>
ON  <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>
AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

— Insert statements for trigger here

END
GO
+++++++++++++++++++++++++++++++++++++++++++++++++++++

改code:

CREATE TRIGGER dbo.CatchStatus
ON  dbo.Ubsubscribe
FOR INSERT,DELETE,UPDATE
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

— Insert statements for trigger here
INSERT INTO T_Unsubscribe (EMAIL, TYPE, DATE, ACTIVE, T_DATE, T_TYPE)
SELECT EMAIL, TYPE, DATE, ACTIVE, GETDATE(), ‘INSERT’ FROM INSERTED
SELECT EMAIL, TYPE, DATE, ACTIVE, GETDATE(), ‘DELETE’ FROM DELETED

END

+++++++++++++++++++++++++++++++++++++++++++++++++

execute , 之後你就會見到trigger 個度多左個野ga la~:) ~完成了~~

dbtrigger2

Entry filed under: Database. Tags: , , , , .

search, filter, paging, sorting in gridview via ADO.NET(code) SQL Statement (Top 5)

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com 標誌

您的留言將使用 WordPress.com 帳號。 登出 /  變更 )

Google photo

您的留言將使用 Google 帳號。 登出 /  變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 /  變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 /  變更 )

連結到 %s

Trackback this post  |  Subscribe to the comments via RSS Feed


近期文章


%d 位部落客按了讚: