Home > SQL Server > ตัวอย่างการสร้าง DDL Trigger เพื่อตรวจสอบ (Monitor, Logging) ใน SQL Server

ตัวอย่างการสร้าง DDL Trigger เพื่อตรวจสอบ (Monitor, Logging) ใน SQL Server


SQL Version: 2005 and later

ใน SQL Server 2005 ขึ้นไป ทาง microsoft ได้เพิ่ม feature ขึ้นมาอยู่ตัวนึงซึ่งถือว่าประทับใจมากครับ นั่นก็คือ DDL Trigger ชื่อก็บอกอยู่แล้วครับว่า DDL ก็หมายถึง Data Definition Language แปลรวมๆ แบบง่ายๆ ก็หมายความว่า “ตัวดักเหตุการณ์เมื่อมีการแก้ไขโครงสร้าง”

แล้วถ้าถามต่อว่า DDL Trigger มีไว้ใช้ทำอะไร คำตอบก็คือ

1. Monitoring

2. Logging

โดยสามารถที่จะสร้างเพื่อดักในระดับ Database หรือ Server ได้ เช่น

CREATE TRIGGER DemoTrigger
ON DATABASE

หรือ

CREATE TRIGGER DemoTrigger
ON SERVER

และจะต้องระบุ Event ที่ต้องการดักจับ โดยสามารถดูได้จากตาราง

DDL Statements with Database Scope

CREATE_APPLICATION_ROLE ALTER_APPLICATION_ROLE DROP_APPLICATION_ROLE
CREATE_ASSEMBLY ALTER_ASSEMBLY DROP_ASSEMBLY
ALTER_AUTHORIZATION_DATABASE
CREATE_CERTIFICATE ALTER_CERTIFICATE DROP_CERTIFICATE
CREATE_CONTRACT DROP_CONTRACT
GRANT_DATABASE DENY_DATABASE REVOKE_DATABASE
CREATE_EVENT_NOTIFICATION DROP_EVENT_NOTIFICATION
CREATE_FUNCTION ALTER_FUNCTION DROP_FUNCTION
CREATE_INDEX ALTER_INDEX DROP_INDEX
CREATE_MESSAGE_TYPE ALTER_MESSAGE_TYPE DROP_MESSAGE_TYPE
CREATE_PARTITION_FUNCTION ALTER_PARTITION_FUNCTION DROP_PARTITION_FUNCTION
CREATE_PARTITION_SCHEME ALTER_PARTITION_SCHEME DROP_PARTITION_SCHEME
CREATE_PROCEDURE ALTER_PROCEDURE DROP_PROCEDURE
CREATE_QUEUE ALTER_QUEUE DROP_QUEUE
CREATE_REMOTE_SERVICE_BINDING ALTER_REMOTE_SERVICE_BINDING DROP_REMOTE_SERVICE_BINDING
CREATE_ROLE ALTER_ROLE DROP_ROLE
CREATE_ROUTE ALTER_ROUTE DROP_ROUTE
CREATE_SCHEMA ALTER_SCHEMA DROP_SCHEMA
CREATE_SERVICE ALTER_SERVICE DROP_SERVICE
CREATE_STATISTICS DROP_STATISTICS UPDATE_STATISTICS
CREATE_SYNONYM DROP_SYNONYM
CREATE_TABLE ALTER_TABLE DROP_TABLE
CREATE_TRIGGER ALTER_TRIGGER DROP_TRIGGER
CREATE_TYPE DROP_TYPE
CREATE_USER ALTER_USER DROP_USER
CREATE_VIEW ALTER_VIEW DROP_VIEW
CREATE_XML_SCHEMA_COLLECTION ALTER_XML_SCHEMA_COLLECTION DROP_XML_SCHEMA_COLLECTION

DDL Statements with Server Scope

ALTER_AUTHORIZATION_SERVER
CREATE_DATABASE ALTER_DATABASE DROP_DATABASE
CREATE_ENDPOINT DROP_ENDPOINT
CREATE_LOGIN ALTER_LOGIN DROP_LOGIN
GRANT_SERVER DENY_SERVER REVOKE_SERVER

แต่เราจำเป็นที่จะต้องทราบโครงสร้างของ event เมื่อ trigger ทำงานแล้วจะส่งข้อมูลตามโครงสร้างนี้ออกมา

<EVENT_INSTANCE>

<EventType>CREATE_TABLE</EventType>

<PostTime>2007-02-22T07:40:03.243</PostTime>

<SPID>53</SPID>

<ServerName>MIAMI</ServerName>

<LoginName>MIAMI\Student</LoginName>

<UserName>dbo</UserName>

<DatabaseName>AdventureWorks</DatabaseName>

<SchemaName>dbo</SchemaName>

<ObjectName>Test</ObjectName>

<ObjectType>TABLE</ObjectType>

<TSQLCommand>

<SetOptions ANSI_NULLS=”ON” ANSI_NULL_DEFAULT=”ON”

ANSI_PADDING=”ON” QUOTED_IDENTIFIER=”ON” ENCRYPTED=”FALSE”/>

<CommandText>

CREATE TABLE Product

(ProductID int,

ProductName vrachar(25))

</CommandText>

</TSQLCommand>

</EVENT_INSTANCE>

ตัวอย่างการสร้าง DDL Trigger เพื่อ monitor เวลามีคนมาสร้าง Table ใหม่

CREATE TRIGGER CreateTable
ON DATABASE
FOR CREATE_TABLE
AS
DECLARE @data xml
DECLARE @posttime nvarchar(24)
DECLARE @database nvarchar(100)
DECLARE @newtable nvarchar(100)
SET @data = eventdata()
SET @posttime = @data.value(‘(/EVENT_INSTANCE/PostTime)[1]’, ‘nvarchar(24)’)
SET @database = @data.value(‘(/EVENT_INSTANCE/DatabaseName)[1]’, ‘nvarchar(100)’)
SET @newtable = @data.value(‘(/EVENT_INSTANCE/ObjectName/text())[1]’, ‘nvarchar(100)’)
PRINT @posttime
PRINT @database
PRINT @newtable

หลักสูตรอบรม (click)

Categories: SQL Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: