Sunday, May 6, 2018

Logon triggers



As the name suggests  these triggers are invoked at logon event, theses event are raised after authentication but before user session is established



Example : Below example checks count of user session on the server and  rollbacks back if the session count is all ready greater than 100


CREATE TRIGGER limit_100_onnection_trigger 
ON ALL SERVER
FOR LOGON 
AS 
BEGIN 
IF (SELECT COUNT(*) FROM sys.dm_exec_sessions 
            WHERE is_user_process = 1 ) > 100 
    ROLLBACK; 
END;

Posted by at No comments:

DDL - Triggers

DDL triggers are kind of triggers which gets  invoked on DDL events such as (CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS statements).

DDL triggers can be defined at Database levels (Database triggers) and server levels (server triggers)

Syntax :

Create Trigger [NAME]
ON [Database | ALL SERVER]
[FOR Event_name ]
As
BEGIN
[SQL CODE]

End

Database scope level example :

CREATE TRIGGER [drop_table]  
ON DATABASE  
FOR DROP_Table 
AS  
   RAISERROR ('can''t drop table',10, 1) 
   ROLLBACK 
GO 

Above trigger will not let any tables to be dropped in the databases
DROP TABLE [dbo].[Person_Audit]
GO
can't drop table
Msg 3609, Level 16, State 2, Line 3
The transaction ended in the trigger. The batch has been aborted.

Server scope trigger  example :
CREATE TRIGGER Create_database_trigger 
ON ALL SERVER  
FOR CREATE_DATABASE  
AS  
    PRINT 'Database Created on ' + cast( getdate() as varchar(30))  
   
GO 


CREATE database [rain_or_shine]


Database Created on May  7 2018  2:08AM
Posted by at No comments:
Labels:

Tiggers

Tiggers are special type of store procedures that gets automatically executed when curtain events occurs on database server

Tiggers are classified into 3 types


DML triggers gets fired  when users  manipulate the data in table  using (insert, update, delete ) statements

Synatx :
Create Trigger [NAME]
on [TABLE]
[FOR | AFTER | INSTEAD OF]
As
BEGIN
[SQL CODE]


END

DML triggers are of two types  instead of triggers and after trigger
Instead of trigger: Are type of triggers which would  execute  the trigger code instead of DML event (insert delete update)

Instead of insert tigger, To demonstrate lets create two tables (1. main table 2. audit table to capture information )  and tigger on the primary table

CREATE TABLE [dbo].[person](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL
) ON [PRIMARY]
GO
 CREATE TABLE [dbo].[Person_Audit](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL,
       [Audit_info] varchar(30) Null
) ON [PRIMARY]
GO

CREATE Trigger dbo.person_insteadoftrigger_insert
ON  [dbo].[person]
INSTEAD of INSERT
AS
Begin
insert into dbo.person_audit
select [BusinessEntityID],[FirstName],[LastName],'Instead_of_insert'
from inserted
End


SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit

BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------

(0 rows affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------

(0 rows affected)
INSERT INTO DBO.person VALUES (1,'SANDEEP','RAIN_OR_SINE')

(1 row affected)

(1 row affected)

SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit
BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------

(0 rows affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE                   Instead_of_insert

(1 row affected)

Instead of delete tigger, To demonstrate lets create two tables (1. main table 2. audit table to capture information )  and tigger on the primary table 

 IF OBJECT_ID('dbo.person') is not null
DROP TABLE [dbo].[person]
GO

CREATE TABLE [dbo].[person](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL
) ON [PRIMARY]
GO



IF OBJECT_ID('dbo.Person_Audit') is not null
DROP TABLE [dbo].[person]
GO

CREATE TABLE [dbo].[Person_Audit](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL,
       [Audit_info] [varchar](30) NULL
) ON [PRIMARY]
GO
CREATE trigger dbo.person_insteadoftrigger_delete
ON  [dbo].[person]
INSTEAD of DELETE
AS
Begin
insert into dbo.person_audit
select [BusinessEntityID],[FirstName],[LastName],'Instead_of_delete'
from deleted
End

 INSERT INTO DBO.person VALUES (1,'SANDEEP','RAIN_OR_SINE')

SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit
BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------

(0 rows affected)

DELETE FROM DBO.person
WHERE BusinessEntityID = 1
(1 row affected)

(1 row affected)

We can see below here that, when we have tried to delete the row from the person table, it instead of deleting from person table it inserted a record in our audit table 
SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit
BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE                   Instead_of_delete

(1 row affected)

Instead of update tigger, To demonstrate lets create two tables (1. main table 2. audit table to capture information )  and tigger on the primary table 

 IF OBJECT_ID('dbo.person') is not null
DROP TABLE [dbo].[person]
GO

CREATE TABLE [dbo].[person](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL
) ON [PRIMARY]
GO



IF OBJECT_ID('dbo.Person_Audit') is not null
DROP TABLE [dbo].[person]
GO

CREATE TABLE [dbo].[Person_Audit](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL,
       [Audit_info] [varchar](30) NULL
) ON [PRIMARY]
GO

CREATE trigger dbo.person_insteadoftrigger_update
ON  [dbo].[person]
INSTEAD of update
AS
Begin
insert into dbo.person_audit
select [BusinessEntityID],[FirstName],[LastName],'Before_update'
from deleted
UNION
select [BusinessEntityID],[FirstName],[LastName],'After_update'
from inserted
End

INSERT INTO DBO.person VALUES (1,'SANDEEP','RAIN_OR_SINE')

SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit
BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------

(0 rows affected)

Update dbo.person
set FirstName = 'SQL SERVER'
where BusinessEntityID = 1

(2 rows affected)

(1 row affected)

We can see below here that, when we have tried to update the row from the person table, it instead of updating from person table it inserted a record in our audit table before and after value of what begin updated 
SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit
BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE                   Instead_of_delete

(1 row affected)


After trigger  is the one which execute trigger code after executing DML action (insert update delete )


After insert tigger, To demonstrate lets create two tables (1. main table 2. audit table to capture information )  and tigger on the primary table

CREATE TABLE [dbo].[person](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL
) ON [PRIMARY]
GO
 CREATE TABLE [dbo].[Person_Audit](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL,
       [Audit_info] varchar(30) Null
) ON [PRIMARY]
GO

CREATE Trigger dbo.person_After_insert
ON  [dbo].[person]
For INSERT
AS
Begin
insert into dbo.person_audit
select [BusinessEntityID],[FirstName],[LastName],'After_insert'
from inserted
End


SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit

BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------

(0 rows affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------

(0 rows affected)
INSERT INTO DBO.person VALUES (1,'SANDEEP','RAIN_OR_SINE')
(1 row affected)

(1 row affected)

SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit
BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE                   After_insert

(1 row affected)

After delete tigger, To demonstrate lets create two tables (1. main table 2. audit table to capture information )  and tigger on the primary table 

 IF OBJECT_ID('dbo.person') is not null
DROP TABLE [dbo].[person]
GO

CREATE TABLE [dbo].[person](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL
) ON [PRIMARY]
GO



IF OBJECT_ID('dbo.Person_Audit') is not null
DROP TABLE [dbo].[person]
GO

CREATE TABLE [dbo].[Person_Audit](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL,
       [Audit_info] [varchar](30) NULL
) ON [PRIMARY]
GO
CREATE Trigger dbo.person_After_Delete
ON  [dbo].[person]
For DELETE
AS
Begin
insert into dbo.person_audit
select [BusinessEntityID],[FirstName],[LastName],'After_delete'
from deleted
End

INSERT INTO DBO.person VALUES (1,'SANDEEP','RAIN_OR_SINE')


SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit
BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------

(0 rows affected)

DELETE FROM DBO.person
WHERE BusinessEntityID = 1
(1 row affected)

(1 row affected)

We can see below here that, when we have tried to delete the row from the person table, it deleted from person table and  it inserted a record in our audit table 
SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit
BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------

(0 rows affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE                   After_delete


(1 row affected)

After update tigger, To demonstrate lets create two tables (1. main table 2. audit table to capture information )  and tigger on the primary table 

 IF OBJECT_ID('dbo.person') is not null
DROP TABLE [dbo].[person]
GO

CREATE TABLE [dbo].[person](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL
) ON [PRIMARY]
GO



IF OBJECT_ID('dbo.Person_Audit') is not null
DROP TABLE [dbo].[person]
GO

CREATE TABLE [dbo].[Person_Audit](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL,
       [Audit_info] [varchar](30) NULL
) ON [PRIMARY]
GO

CREATE Trigger dbo.person_After_update
ON  [dbo].[person]
For update
AS
Begin
insert into dbo.person_audit
select [BusinessEntityID],[FirstName],[LastName],'Before_update'
from deleted
union
select [BusinessEntityID],[FirstName],[LastName],'After_update'
from inserted

End

INSERT INTO DBO.person VALUES (1,'SANDEEP','RAIN_OR_SINE')

SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit
BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------

(0 rows affected)

Update dbo.person
set FirstName = 'SQL SERVER'
where BusinessEntityID = 1

(2 rows affected)

(1 row affected)

We can see below here that, when we have tried to update the row from the person table, it updated row from person table and  it inserted a records in our audit table before and after value of what begin updated 
SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit
BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------
1                SQL SERVER                     RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE                   Before_update
1                SQL SERVER                     RAIN_OR_SINE                   After_update


(2 rows affected)

DDL



  
Posted by at 1 comment:
Labels:
Subscribe to: Posts (Atom)