Friday, March 7, 2014

How to create / insert / update Trigger on SQL Server 2012

CREATE TRIGGER trginsert ON dbo.tblEmployee
FOR INSERT
AS
DECLARE @recipients VARCHAR(100)
, @subject VARCHAR(100) , @message  VARCHAR(100)
, @body VARCHAR(2000)

 EXEC master..xp_sendmail
            @recipients = 'ramachandran.ponnusamy@abc.com',
            @subject = 'Customer Information Added....',
              @message = @body
        PRINT 'Welcome new employee information....'
SELECT * FROM tblEmployee


-- Ex --- INSERT INTO tblEmployee VALUES(5,'cccd',5487)

------------------ Update Trigger ---------------
CREATE TRIGGER trgUpdate ON dbo.tblEmployee
FOR UPDATE
AS
Print 'Updated employee information....'
SELECT * FROM tblEmployee
-- Ex --- UPDATE tblEmployee SET ename='dadsas' WHERE Eid = 3
-- Ex --- INSERT INTO tblEmployee VALUES(5,'cccd',5487)

-----------------Delete Trigger -----------
CREATE TRIGGER trgDelete ON dbo.tblEmployee
FOR DELETE
AS
Print 'Deleted employee information....'
-- Ex --- DELETE FROM tblEmployee WHERE Eid = 3