Archive for the ‘Database’ Category

SQL Server 2005 Trigger

December 15, 2008

this article is about sql server 2005 trigger, update ,insert ,delete, and logon trigger. Although not much, hope this can shed some light. BTW, i have not tested the coding especially about the logon trigger cos i haven’t got Ms SQL Server SP2. 

Here is my compilation about trigger. Happy reading… :)

Trigger adalah fitur di MsSQL Server 2005 yang merespon terhadap event yang terjadi di dalam suatu database. Logon Trigger mulai diperkenalkan di MsSql Server 2005 SP 2. Di Oracle, trigger ini sudah ada sejak lama.

Tabel yang menyimpan informasi tentang siapa saja yg logon adalah sys.dm_exec_sessions. Atau dapat juga dilihat dengan menjalankan kode sql “EXEC sp_who” atau “EXEC sp_who2”. Saya sendiri masih belum mudheng yang mana – yang mana.. J

Oya, utk instalasi MSSQLServer 2005 ini butuh Microsoft Windows Installer 3.1. Download dulu programnya.Kecil kok, sekitar 2.5Mb.

FYI, MsSQL Server 2000 belum mendukung Logon Trigger

1. Membuat trigger saat update, insert :

CREATE TRIGGER DataTable_Audit on DataTable

FOR INSERT, UPDATE  saat update dan insert
AS IF UPDATE(MonitoredColumn)
INSERT INTO myAuditTable (USERLOGIN, ChangeDT)
VALUES (SYSTEM_USER, getdate()) — login si user dimasukkan ke tabel
GO

Untuk men-test script di atas, gunakan script di bawah ini :

DECLARE @ID int
INSERT INTO DataTable (UnMonitoriedColumn, MonitoredColumn)
VALUES (‘ABC’, ‘DEF’)
SELECT @ID = SCOPE_IDENTITY()
UPDATE DataTable
SET MonitoredColumn = ‘GHI’
WHERE [Id] = @ID
UPDATE DataTable
SET UnMonitoriedColumn = ‘JKL’
WHERE [Id] = @ID
SELECT * FROM myAuditTable
GO

 Hasilnya adalah :

 AuditEventID USERLOGIN ChangeDT

———— ——————– ——————————————————
1 NSandrew 2005-07-18 03:13:20.403
2 NSandrew 2005-07-18 03:13:20.403
(2 row(s) affected)

Statement update yg kedua TIDAK mengubah isi dari MonitoredCloumn, jadi hanya 2 event saja yang tercatat yaitu INSERT dan UPDATE untuk MonitoredColoumn.

Untuk memudahkan pemahaman, ada versi sederhana seperti di bawah:

USE db1;

GO

 CREATE TRIGGER new_user ON nama_tabel — ini koding utk membuat trigger

AFTER INSERT

AS

  PRINT ‘New users added.’;

GO

 SELECT * FROM sys.triggers — melihat trigger yang ada di tabel tersebut

GO

Hasilnya adalah :

name         object_id   parent_id   type type_desc  

———— ———– ———– —- ————

dml_message  690101499   674101442   TR   SQL_TRIGGER

new_user     706101556   674101442   TR   SQL_TRIGGER

2. Membuat trigger saat Logon event terjadi (MsSQL Server 05 SP 2)

 Ketika ada event logon, maka informasi user tersebut akan dimasukkan ke table lain.

Gunakan koding di bawah

 USE master;

GO
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,
    CHECK_EXPIRATION = ON;
GO
GRANT VIEW SERVER STATE TO login_test;
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'login_test') > 3
    ROLLBACK;
END;
Koding di atas akan menolak login utk suatu user tertentu jika user tersebut sudah membuat login sebanyak 3 kali.

Atau bisa juga dengan koding di bawah. Koding ini akan mencatat informasi dari user yang masuk ke database ke dalam suatu tabel.

 CREATE TRIGGER [connection_limit_triggerDDLTRIGGER]

ON ALL SERVER WITH EXECUTE AS ‘login_testDDLTRIGGER’

FOR LOGON

AS

BEGIN

IF ORIGINAL_LOGIN()= ‘login_testDDLTRIGGER’ AND

    (SELECT COUNT(*) FROM sys.dm_exec_sessions

            WHERE is_user_process = 1 AND

                original_login_name = ‘login_testDDLTRIGGER’) > 1

    BEGIN

      DECLARE @data XML

      SET @data = EVENTDATA()

      INSERT ray_ddl_log (PostTime, DB_User, Event, TSQL)

       VALUES

       (GETDATE(),

       CONVERT(nvarchar(100), CURRENT_USER),

       @data.value(‘(/EVENT_INSTANCE/EventType)[1]‘, ‘nvarchar(100)’),

       @data.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]‘, ‘nvarchar(2000)’) )

     END

END;

GO

3. SQLExpress tidak bisa menangani event logon. Maka koding di bawah dapat digunakan:

CREATE TRIGGER trgLogDDLEvent ON DATABASE 

    FOR DDL_DATABASE_LEVEL_EVENTS
AS
    DECLARE @data XML
    SET @data EVENTDATA()
    IF @data.value('(/EVENT_INSTANCE/EventType)[1]''nvarchar(100)')
        <> 'CREATE_STATISTICS' 
        INSERT  INTO DDLChangeLog
                (
                  EventType,
                  ObjectName,
                  ObjectType,
                  tsql
                )
        VALUES  (
                   @data.value('(/EVENT_INSTANCE/EventType)[1]',
                              'nvarchar(100)'),
                  @data.value('(/EVENT_INSTANCE/ObjectName)[1]',
                              'nvarchar(100)'),
                  @data.value('(/EVENT_INSTANCE/ObjectType)[1]',
                              'nvarchar(100)'),
                  @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]',
                              'nvarchar(max)')
                ) ;
GO

4. Bagaimana cara melihat daftar event (event list) dari trigger yang sudah ada (existing trigger) dengan menggunakan sys.trigger_events ?

Kamu bisa menggabungkan sys.trigger_events dan sys.trigger. Kodingnya adalah sebagai berikut :

 USE db1; — ini adalah nama database

GO

 SELECT t.name, e.type, e.type_desc

FROM sys.trigger_events AS e, sys.triggers AS t

WHERE e.object_id = t.object_id

GO

Hasilnya adalah :

name           type   type_desc

————– —— ———

dml_message    1      INSERT

dml_message    2      UPDATE

dml_message    3      DELETE

new_user       1      INSERT

(4 row(s) affected)

5. Menyimpan event ALTER TABLE ke dalam suatu table. 

Buat dulu table yang akan menyimpan informasi user nya dengan koding berikut :

USE Northwind; 
GO 
CREATE TABLE EvtLog 

   
PostTime DATETIME
   
LoginName NVARCHAR(100), 
   
EventType NVARCHAR(100), 
   
TSQLCommand NVARCHAR(2000)  

GO 

Buat DDL Trigger nya :

CREATE TRIGGER trPreventTblChange  
ON DATABASE  
FOR 
ALTER_TABLE 
AS 
DECLARE 
@Data XML 
SET @Data EventData() 
INSERT EvtLog (PostTimeLoginNameEventTypeTSQLCommand)  
VALUES  
   
(GETDATE(),  
   
CONVERT(NVARCHAR(100), CURRENT_USER),  
   
@Data.value('(/EVENT_INSTANCE/EventType)[1]''nvarchar(100)'),  
   
@Data.value('(/EVENT_INSTANCE/TSQLCommand)[1]''nvarchar(2000)') ) ; 
GO

Lalu tes :

USE Northwind 
GO 

ALTER TABLE Region 
ADD newColumn SMALLDATETIME NULL 

Lihat hasil triggernya dengan melihat langsung ke table yang dibuat tadi:

SELECT * FROM Northwind.dbo.evtlog

6. Memasukkan data ke table TempSPWHO2 dengan menggunakan EXEC 

CREATE TABLE TempSPWHO2

(

SPID1 INT,

Status VARCHAR(60),

Login VARCHAR(16),

Hostname VARCHAR(256),

BlkBy VARCHAR(3),

DBName VARCHAR(256),

Command VARCHAR(32),

CPUTime INT,

DiskIO INT,

LastBatch VARCHAR(16),

ProgramName VARCHAR(256),

SPID2 INT

)

GO

INSERT INTO TempSPWHO2

EXEC sp_who2

GO


My Final Question :

Kalau ingin melihat informasi user harus menggunakan yg mana ??? Hayoo yang mana… anyone knows ?

 

Informasi yang ada di sql command EXEC sp_who,

Informasi yang ada di sql command EXEC sp_who2,

EventData(),

Atau

sys.dm_exec_sessions

 

yocki@hotmail.com. Compiled from many sources. Dikompile dari berbagai sumber.

Special thanks to Kasim Wirama (wkww2004@yahoo.com) and 4guys@npond.com. Feel free to contact them. Don’t contact me cos iam not the MVP.



Follow

Get every new post delivered to your Inbox.