Set update trigger to keep watch on certain column’s update in SQL Server 2008

I have seen many times that people keep asking the question how to track the updates in certain column.Well there are several ways to do so but the oldest way is to set UPDATE trigger on the table and keep an eye on the important column.

Please be informed that excesses use of trigger may affect performance so use this method carefully not for keeping track of so many columns in so many tables.

Anyway, let us get back to our core topic and see one small script.

–create one table for demonstration

CREATE TABLE TriggerTest

(

UserName varchar(20),

[Password] varchar(20)

)

GO

–create one table which will LOG information

CREATE TABLE TriggerTestLog

(

ID INT identity(1,1),

UserName varchar(20),

OldPassword varchar(20),

NewPassword varchar(20),

DateOfUpdate datetime

)

GO

–insert some records in first table

INSERT INTO TriggerTest

SELECT ‘ritesh’,‘shah’ union all

SELECT ‘rajan’,‘shah’ union all

SELECT ‘bihag’,‘goodluck’ union all

SELECT ‘bhaumik’,‘shruti’

GO

–set after update trigger for first table

–when password will be updated, it will log

–information in second table.

Create TRIGGER TrgTriggerTest ON TriggerTest

   AFTER Update

AS

BEGIN

      SET NOCOUNT ON;

      IF UPDATE([Password])

      BEGIN

            insert into TriggerTestLog(username,OldPassword,NewPassword,dateOfUpdate)

            select i.username,d.[password],i.[password],getdate()

            from inserted i join deleted d on i.username=d.username

      END

END

GO

–select records from second table

–which is blank right now ofcourse

select * from TriggerTestLog

GO

–update first table

UPDATE TriggerTest SET [Password]=‘furious’ WHERE UserName=‘ritesh’

GO

–you will get information automatically inserted in second table.

select * from TriggerTestLog

GO

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Set update trigger to keep watch on certain column’s update in SQL Server 2008

I have seen many times that people keep asking the question how to track the updates in certain column.Well there are several ways to do so but the oldest way is to set UPDATE trigger on the table and keep an eye on the important column.

Please be informed that excesses use of trigger may affect performance so use this method carefully not for keeping track of so many columns in so many tables.

Anyway, let us get back to our core topic and see one small script.

–create one table for demonstration

CREATE TABLE TriggerTest

(

UserName varchar(20),

[Password] varchar(20)

)

GO

–create one table which will LOG information

CREATE TABLE TriggerTestLog

(

ID INT identity(1,1),

UserName varchar(20),

OldPassword varchar(20),

NewPassword varchar(20),

DateOfUpdate datetime

)

GO

–insert some records in first table

INSERT INTO TriggerTest

SELECT ‘ritesh’,‘shah’ union all

SELECT ‘rajan’,‘shah’ union all

SELECT ‘bihag’,‘goodluck’ union all

SELECT ‘bhaumik’,‘shruti’

GO

–set after update trigger for first table

–when password will be updated, it will log

–information in second table.

Create TRIGGER TrgTriggerTest ON TriggerTest

   AFTER Update

AS

BEGIN

      SET NOCOUNT ON;

      IF UPDATE([Password])

      BEGIN

            insert into TriggerTestLog(username,OldPassword,NewPassword,dateOfUpdate)

            select i.username,d.[password],i.[password],getdate()

            from inserted i join deleted d on i.username=d.username

      END

END

GO

–select records from second table

–which is blank right now ofcourse

select * from TriggerTestLog

GO

–update first table

UPDATE TriggerTest SET [Password]=‘furious’ WHERE UserName=‘ritesh’

GO

–you will get information automatically inserted in second table.

select * from TriggerTestLog

GO

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Email from INSERT trigger with msdb.dbo.sp_send_dbmail in SQL Server 2005

Sometime we need to send email from database based on specific condition. How can we cater this need?

I have just finished this task in one of our databases. I have to send an email to specific email address when particular records getting inserted in specific table. Trigger is a best option to check whether an inserted record falls under our criteria or not. Moreover I have used msdb.dbo.sp_send_dbmail to send an email.

Note: msdb.dbo.sp_send_dbmail will work only if you have enable database mail, as msdb.dbo.sp_send_dbmail will send an email from the profile set in database email.

Let us create one example to do so.

USE [AdventureWorks]

GO

–create table

USE [adventureworks]

GO

CREATE TABLE [dbo].[emps](

[Name] [varchar](50),

[Dept] [varchar](10),

[Company] [varchar](15)

) ON [PRIMARY]

GO

–insert records

INSERT INTO emps

SELECT ‘RITESH’,‘MIS’,‘ECHEM’ UNION ALL

SELECT ‘Rajan’,‘MIS’,‘mar’

–create one trigger which will check inserted record from schedo table “INSERTED”

–and send an email with msdb.dbo.sp_send_dbmail

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [TR_EMPS_INSERT]

ON [emps]

FOR INSERT

AS

DECLARE @Dept varchar(6)

DECLARE @Name VARCHAR(10)

DECLARE @Bod VARCHAR(MAX)

DECLARE @Sub VARCHAR(100)

BEGIN

SELECT @Dept=Dept,@Name=Name FROM INSERTED

IF @Dept=‘MIS’

BEGIN

SELECT @Sub=‘New employee in MIS department’

SELECT @Bod=‘Hello, <BR><BR>New Employee (‘+ @Name +‘) has been recruted in MIS department ‘

EXEC msdb.dbo.sp_send_dbmail @recipients=‘Rits4Friends@gmail.com’,@copy_recipients=‘ritesh_a_shah@yahoo.com’,

@subject = @SUB,

@body = @BOD,

@body_format = ‘HTML’;

END

END

Happy Triggering!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Auditing Trail with Trigger in SQL Server 2005

If you are new to this concept in database world than you might think what is Auditing Trail? The answer is pretty much easy and simple, if you want to increase the data-integrity and wants to have full details about insertion, edition and deletion of your data; Audit Trail is the concept you have to adopt.

Since this is a very huge topic and not possible to cover complete topic in one or may be in few articles, I will give some basic details here and will write some more article with live situation as and when time permits.

You would like to keep track of total update and delete in records after its insert, you may want which front-end accessed your row for modification, which user has changed which records.

There are many methods popular to keep track of your records change like you can have duplicate table for each table along with some comment field sand keep the track and old value. You may wish to create one table which will have details off all tables and about every single transaction but In that case, maintenance of that table is very crucial.

Let us create one table and try to keep trail of database.

–demo table for AuditTrail

–Note: this is just for demo purpose, in live situation we may have very big table than
— on explained here. everybody has their own need and situation.
CREATE TABLE AuditTrail(
ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL CONSTRAINT def_AuditTrail_Id DEFAULT(NEWID()),
AuditDate DATETIME,
TableName VARCHAR(50) NOT NULL,
ColumnName VARCHAR(50) NOT NULL,
Description VARCHAR(50) NOT NULL,
UserName VARCHAR(50) NOT NULL,
OldValue VARCHAR(50) NOT NULL,
NewValue VARCHAR(50) NOT NULL
)

–Now we will create one more table for which we will keep the trail in above table
–You can keep Audit of Insert, Delete and Update everything in above table
–and can customize it as per your need
CREATE TABLE AuditDemo
(
ID INT IDENTITY(1,1) NOT NULL,
Name VARCHAR(10),
Company VARCHAR(10)

— have few records in above table

INSERT INTO AuditDemo
SELECT ‘Ritesh’,‘eChem’ UNION ALL

SELECT ‘Rajan’,‘Marwadi’

–LET us create fixed audit trail trigger
create TRIGGER AUDITonAuditDemo
ON AuditDemo
AFTER UPDATE
AS
IF UPDATE(Name)
BEGIN
INSERT AuditTrail
(AuditDate,TableName,ColumnName,Description,UserName,OldValue,NewValue)
SELECT
GETDATE(),‘AuditDemo’,‘Name’,‘Update’,suser_sname(),DELETED.Name,INSERTED.Name
FROM INSERTED
JOIN DELETED
ON INSERTED.ID=DELETED.ID
END

IF UPDATE(company)
BEGIN
INSERT AuditTrail
(AuditDate,TableName,ColumnName,Description,UserName,OldValue,NewValue)
SELECT
GETDATE(),‘AuditDemo’,‘Company’,‘Update’,suser_sname(),Deleted.Company,Inserted.Company
FROM INSERTED
JOIN DELETED
ON INSERTED.ID=DELETED.ID

END

–let us update record in AuditDemo tabel

UPDATE AuditDemo SET Name=‘R.Shah’ WHERE ID=2

–LET US NOW CHECK WHETHER WE HAVE TRAILED THE RECORDS OR NOT

SELECT * FROM AuditTrail

–let use update and check once agian

UPDATE AuditDemo SET Company=‘Testing’

SELECT * FROM AuditTrail

Hope you have enjoyed!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Instead Of Trigger in SQL Server 2005

As name said “Instead Of” when you submit any transaction, “Instead of” trigger get fire first instead of your submitted DML processes further. You can define only one “Instead Of” Trigger for one table or one view.

–create table for demo

CREATE TABLE empData

(

Name varchar(10),

Age INT

)

–create INSTEAD OF trigger on empData table,

–which will show message if Age > 100

–and roll back transaction

alter TRIGGER empAgeCheck ON empData

INSTEAD OF INSERT

AS

SET NOCOUNT ON

DECLARE @Age INT

SELECT @Age=Age FROM INSERTED

IF (@Age>100)

BEGIN

print ‘you can not do this’

ROLLBACK

END

GO

–try inserting records and see message

INSERT INTO empData VALUES(‘TestName’,101)

–finally check your data in empData table.

select * from empData

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

After Trigger in Microsoft SQL Server 2005 (CREATE and ALTER)

After giving quiet a good details about Triggers in my previous article, I would like to move further and want to give introduction about After Trigger in SQL Server.

AFTER Trigger was the only trigger before Microsoft SQL Server 2000 and it is useful as well. Table can contain more than one AFTER trigger. You can use AFTER trigger for recording data audit trails, complex business rule and for complex data validation.

AFTER trigger fire after all transaction gets complete respected to DML command but before COMMIT.

–create table for demo

CREATE TABLE empData

(

Name varchar(10),

Age INT

)

–create AFTER trigger on empData table,

–which will show message if Age > 100

–but won’t stop inserting records.

CREATE TRIGGER empAgeCheck ON empData

AFTER INSERT

AS

SET NOCOUNT ON

DECLARE @Age INT

SELECT @Age=Age FROM INSERTED

IF (@Age>100)

BEGIN

print ‘you can not do this’

END

GO

–try inserting records and see message

INSERT INTO empData VALUES(‘TestName’,101)

–if you want to abort the batch

–if it break the rule than ALTER your TRIGGER

–and add rollback in it

ALTER TRIGGER empAgeCheck ON empData

AFTER INSERT

AS

SET NOCOUNT ON

DECLARE @Age INT

SELECT @Age=Age FROM INSERTED

IF (@Age>100)

BEGIN

print ‘you can not do this’

ROLLBACK

END

GO

–try inserting records and see message

INSERT INTO empData VALUES(‘TestName’,101)

–finally check your data in empData table.

select * from empData

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah