articles

Home / DeveloperSection / Articles / Use of DML, DLL and logon trigger in SQL

Use of DML, DLL and logon trigger in SQL

Samuel Fernandes4651 05-Oct-2016

In this blog we are explaining about how to create trigger for DML, DDL and logon server.

What is the trigger?

Trigger is the stored procedure that are automatic executes when any event occur in the database.

What is DML trigger?

DML means data manipulation language. This type of trigger occurs when user tries to manipulate data though data manipulation language.  Insert update or delete are the events comes under category of DML.

It is of two types :
1.    After trigger
2.    Instead of trigger

After trigger logic for insert update and delete

For Insert

Create your table:


create TABLE Info
(
ID INT,
NAME VARCHAR(100),
)
 
Create audit table
 
create TABLE AUDIT_TABLE3
(
ID INT,
NAME VARCHAR(100),
Timestampinsert datetime,
message_audit varchar(100),
)
 
Create the trigger for your info table
Create trigger infotrigger on  info
for insert
AS
declare @ID int;
declare @NAME varchar(100);
 
declare         @message_audit varchar(100);
 
select @ID=I.ID from inserted I
SELECT @NAME= I.NAME
FROM INSERTED I
SET @message_audit='inserted
successfully, your action is recorded';
insert into AUDIT_TABLE3
(ID,[NAME],Timestampinsert,[message_audit])
values (@ID,@NAME,GETDATE(),@message_audit)
 
PRINT 'inserted successfully, your action is recorded'
GO
How to create a delete trigger on same table
create trigger infotrigger1 on  info
for delete
AS
declare @ID int;
declare @NAME varchar(100);
 
declare         @message_audit varchar (100);
 
select @ID=I.ID from deleted I
SELECT @NAME= I.NAME
FROM deleted I
SET @message_audit='Deleted
successfully, your action is recorded'
insert into AUDIT_TABLE3
(ID,[NAME],Timestampinsert,[message_audit])
values (@ID,@NAME,GETDATE(),@message_audit)
 
PRINT 'Deleted successfully, your action is recorded'
GO
 
How to create a Update trigger on same table
Create trigger infotrigger2 on  info
for update
AS
declare @ID int;
declare @NAME varchar(100);
 
declare         @message_audit varchar(100);
 
select @ID=I.ID from inserted I
SELECT @NAME= I.NAME
FROM inserted I
SET @message_audit='Updated
successfully, your action is recorded';
insert into AUDIT_TABLE3
(ID,[NAME],Timestampinsert,[message_audit])
values (@ID,@NAME,GETDATE(),@message_audit)
 
PRINT 'Updated successfully, your action is recorded'
GO

 

Insert the record in the table
insert into info(id,name)
values (1,'Pri')
Output:
Output:
(1 row(s) affected)
inserted successfully, your action is recorded
 
Delete the record in the table:
delete from info
where name ='All test'

Output:

(1 row(s) affected)
Deleted successfully, your action is recorded
Delete the record in the table:
update info
set name='All'
(1 row(s) affected)
Updated successfully, your action is recorded
Update the in the table
update info
set NAME ='Abhi'
(1 row(s) affected)
Updated successfully, your action is recorded

 

Above example is for the After DML, but there are lots of limitation with this type trigger. It can’t be use for views, if trigger occur and you want rollback after satisfying certain condtion then After DML will not be used.

 

To overcome this limitation we are using Instead of trigger. In this method, we are actually avoiding event processing. The process actually processed by the trigger. Hence, no action is not performed by the event instead of that trigger is responsible for the whole processing. 

Instead of trigger for insert

If you want to insert a record for all customers whose age is greater than 18, and update the database accordingly.

Create a table for customer phone number ,name and age

create Cust_info
(
CUTOMER_PHONE_NUM INT
NAME VARCHAR(100)
AGE INT      
)
 
Create the audit table for
this case
create TABLE AUDIT_TABLE3
(
ID INT,
NAME VARCHAR(100),
Timestampinsert datetime,
message_audit varchar(100),
)
 
Now create the trigger for this case :


 
CREATE TRIGGER insteadoftri ON Cust_info
INSTEAD OF INSERT
AS
declare @ID int;
declare @NAME varchar(100);
declare         @message_audit varchar(100);
declare @temp int;
select @ID=I.CUTOMER_PHONE_NUM
from inserted I
SELECT @NAME=I.NAME
FROM inserted I
select @temp=I.AGE
FROM inserted I
SET @message_audit='INSERTED
successfully, your action is recorded';
IF (@TEMP<18)
BEGIN
RAISERROR('I AM SORRY YOU
AGE IS LESS THAN 18',16,1);
ROLLBACK;
END
ELSE
BEGIN
INSERT INTO Cust_info (CUTOMER_PHONE_NUM,[NAME], AGE)
VALUES (@ID,@NAME,@TEMP)
insert into AUDIT_TABLE3
(ID,[NAME],Timestampinsert,[message_audit])
values (@ID,@NAME,GETDATE(),@message_audit)
PRINT 'Inserted successfully, your action is recorded'
END
GO

 

Verify that it is working or not by inserting the data


 
Age is greater than 18.
 
INSERT INTO Cust_info (CUTOMER_PHONE_NUM,[NAME], AGE)
VALUES (941536,'Abhishek',18)
 
Age is greater than 18.
 
 
INSERT INTO Cust_info (CUTOMER_PHONE_NUM,[NAME], AGE)
VALUES (941536,'Abhishek',17)

 

Similaarly we can create for the Update and delete.

 

What is DDL trigger?

DDL means data definition language. This type of trigger occurs when there is CREATE ALTER or DELETE event occur in the database.

Example:

Creating DDL trigger
CREATE TRIGGER RESTICT_DATABASE  
ON DATABASE  
FOR DROP_TABLE, ALTER_TABLE ,Create_table
AS  
   PRINT ' We are sorry,
transaction can''t be processed , Security Error'  
   ROLLBACK;
 
Verify it by creating
altering or droping the table:
Eg.,
create table ee_empl
(
Num1 int
)

 

 

What is the logon trigger?

This type of trigger occurs in response to Logon event. Logon event is raised when user session established with an instance of SQL Server.

 

CREATE DATABASE NEW_DATABASE
USE NEW_DATABASE
 
CREATE TABLE added_user1
(
  USERid VARCHAR(50),
  ADMINUSER VARCHAR(50),
  LOGTIME DATETIME
)
 
CREATE TRIGGER LOGON_TRIGGER3
ON ALL SERVER FOR LOGON
AS
INSERT INTO NEW_DATABASE.dbo.ADDED_USER1 VALUES (SYSTEM_USER,USER,GETDATE())
GO
 

 

Other Link which may help you



https://www.mindstick.com/Articles/330/triggers-in-sql-server

 

https://www.mindstick.com/Articles/569/trigger-in-sql-server

 

https://www.mindstick.com/Articles/454/trigger-in-sql-server

 

https://www.mindstick.com/Articles/430/implementing-triggers-in-sql-server

 

https://www.mindstick.com/Articles/982/trigger-in-sql-server


Leave Comment

Comments

Liked By