Search

Advertisements

Showing posts with label trigger. Show all posts
Showing posts with label trigger. Show all posts

SQL Server - Audit Trigger



What Audit trigger here will do:

Audit trigger – to audit DML operations on all tables in a database.
Audit trigger is created by using stored procedure “GenerateAudittrail” & function “Upd_trg_col_populate”.

What  stored procedure “GenerateAudittrail” here will do :
 
Create exact copy of all tables in a database with extra audit columns and name suffixed as ‘_audit’.
Create Insert/Update/Delete trigger for all original  tables .
Triggers are created in such a way that whenever DML operations take place on all original  tables ,audit tables will be populated accordingly.

What function “Upd_trg_col_populate” here will do:

In case of update operation,only updated column value of that table along with audit columns will get populated in audit tables
“Upd_trg_col_populate” function is called inside GenerateAudittrail” procedure.


Step 1: --create test data

Sql Server - INSTEAD OF TRIGGER

INSTEAD OF TRIGGER -

 It is used to perform your desired operations instead of INSERT/UPDATE/DELETE on table or view by making use of magic tables which gets populated as a result of DML operations.

EXAMPLE 1:
CREATE DATABASE TEST_INSTEAD_OF_TRG
GO

USE TEST_INSTEAD_OF_TRG
GO

inserting into view which has two or more tables joined using INSTEAD OF TRIGGER

-- Create two tables
-- student table

CREATE TABLE STUDENT(

ID INT,
Name VARCHAR(20))
GO

 
INSERT INTO STUDENT SELECT 1,'sathya'
GO

--department table

CREATE TABLE DEPARTMENT(

ID INT,
DEPT_Name VARCHAR(20))
GO

 
INSERT INTO DEPARTMENT SELECT 1,'ECE'
GO

--create view joining student & department table

CREATE VIEW VW_INSTEAD_OF_TRG
AS
SELECT a.ID,a.Name,b.DEPT_Name FROM STUDENT a 

JOIN DEPARTMENT b ON a.ID = b.ID

-- try to insert into view
INSERT INTO VW_INSTEAD_OF_TRG SELECT 2,'deepak','CS'
GO

you will get the below error message,because you cannot insert into view with two or more tables joined


Msg 4405, Level 16, State 1, Line 1
View or function 'VW_INSTEAD_OF_TRG' is not updatable because the modification affects multiple base tables.


--solution,create instead of trigger,as shown below

CREATE TRIGGER INSTEAD_OF_TRG ON VW_INSTEAD_OF_TRG
INSTEAD OF INSERT
AS
INSERT INTO STUDENT (ID,Name) SELECT ID,Name FROM INSERTED

INSERT INTO DEPARTMENT (ID,DEPT_Name) SELECT ID,DEPT_Name FROM INSERTED

--Again try to insert
INSERT INTO VW_INSTEAD_OF_TRG SELECT 2,'deepak','CS'
GO


--you will notice records inserted into view with joined tables
 
SELECT * FROM VW_INSTEAD_OF_TRG
SELECT * FROM STUDENT
SELECT * FROM DEPARTMENT

EXAMPLE 2:to avoid default values in table getting updated/inserted 


--create table with default value option  for few columns

CREATE TABLE Login_Audit (

ID INT,
NAME VARCHAR(20),
Last_Login_User VARCHAR(20) DEFAULT SYSTEM_USER,
Last_Login_Date DATE DEFAULT GETDATE())
GO


--try to populate the table (including default value column)  with values
 
INSERT INTO Login_Audit (ID,NAME,Last_Login_User,Last_Login_Date) SELECT 1,'Sathya','Hacker',GETDATE()-365
GO

--If you run the below query,you will notice default values getting updated
SELECT * FROM Login_Audit

--Solution,create instead of trigger,as shown below

CREATE TRIGGER INSTEAD_OF_TRGG ON Login_Audit
INSTEAD OF INSERT
AS
INSERT INTO Login_Audit (ID,NAME) SELECT ID,NAME FROM inserted

--try again to override the default values

INSERT INTO Login_Audit (ID,NAME,Last_Login_User,Last_Login_Date) SELECT 1,'Sathya','Hacker',GETDATE()-365
GO

--you will notice ,instead of trigger has prevented default values getting updated
SELECT * FROM Login_Audit



DROP DATABASE TEST_INSTEAD_OF_TRG

Sql Server - DDL triggers & EVENTDATA() function


DDL Trigger:
DDL triggers are fired whenever Data Definition Language (DDL) events  like Create, Alter, Drop, or Data Control Language (DCL) events   like Grant, Deny or Revoke occurs.

 DDL Triggers is useful for the following purpose:
Can be used by DBA ‘s:
• to Issue an alert when a DDL event occurs in Database or Server.
to log information about database-level DDL events and Server-level DDL events.
• to Secure the database or server from malpractice.

DDL triggers scope is of two types:
1.)Server level
2.)Database level

Refer below link,for more information about different  Scope of DDL triggers.

By executing below query,we can get hierarchy list of DDL events & DDL grouped events.
WITH DirectReports(name, parent_type, type, level, sort) AS
(
    SELECT CONVERT(varchar(255),type_name), parent_type, type, 1, CONVERT(varchar(255),type_name)
    FROM sys.trigger_event_types
    WHERE parent_type IS NULL
    UNION ALL
SELECT  CONVERT(varchar(255), REPLICATE ('|   ' , level) + e.type_name),
e.parent_type, e.type, level + 1,
    CONVERT (varchar(255), RTRIM(sort) + '|   ' + e.type_name)
    FROM sys.trigger_event_types AS e
        INNER JOIN DirectReports AS d
        ON e.parent_type = d.type
)
SELECT parent_type, type, name
FROM DirectReports
ORDER BY sort;


Sql server gives flexibility to handle DDL events in DDL triggers,you can see that in the highlighted section of below image.

For example in below image ,

Advertisements