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
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
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
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
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