SQL Server 2008 Trigger - Basic Practice

SQL Server 2008 Trigger - Basic Practice.sql

USE [MyLabDB]
GO

/****** Object:  Table [dbo].[MyTable01]    Script Date: 08/23/2015 22:44:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

/* 我的練習用資料檔 */
CREATE TABLE [dbo].[MyTable01](
	[RowId] [int] NOT NULL,
	[StrCI] [varchar](50) NULL,
	[StrCS] [varchar](50) NULL,
 CONSTRAINT [PK_MyTable01] PRIMARY KEY CLUSTERED 
(
	[RowId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

/* 我的練習用資料檔之資料操作紀錄檔 */
CREATE TABLE [dbo].[MyTable01Log](
	[LogDtm] [datetime] NOT NULL,
	[Action] [char](6) NOT NULL,
	[RowId] [int] NOT NULL,
	[StrCI] [varchar](50) NULL,
	[StrCS] [varchar](50) NULL
) ON [PRIMARY]

GO

/* 使用Trigger執行資料操作紀錄 */
CREATE TRIGGER [dbo].[MyTrigger01] 
  ON [dbo].[MyTable01] 
  FOR INSERT, UPDATE, DELETE 
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @Action CHAR(6);
  
  IF NOT EXISTS (SELECT * FROM inserted)
	SET @Action = 'DELETE'
  ELSE IF NOT EXISTS (SELECT * FROM deleted)
	SET @Action = 'INSERT'
  ELSE
	SET @Action = 'UPDATE';
    
  IF @Action = 'DELETE'
	INSERT INTO MyTable01Log SELECT GetDate(),@Action,* FROM deleted;  
  ELSE
	INSERT INTO MyTable01Log SELECT GetDate(),@Action,* FROM inserted;
  
END;
GO

SET ANSI_PADDING OFF
GO