October 20, 2015 09:29 by
PeterA trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.
There are three types of triggers. Basically triggers are classified in to two main type
- Insert Of Trigger.
- After Trigger.
This After Trigger run after an insert, update, or delete on table. They are not support view.
So we can say that after trigger also classified in to three types:-
- AFTER INSERT trigger.
- AFTER UPDATE trigger.
- AFTER DELETE trigger.
Insert Trigger
Whenever a row is inserted in the Customers Table, the following trigger will be executed. The newly inserted record is available in the INSERTED table. The following Trigger is fetching the CustomerId of the inserted record and the fetched value is inserted in the CustomerLogs table. Now, write the following code:
CREATE TRIGGER [dbo].[Customer_INSERT]
ON [dbo].[Customers]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CustomerId INT
SELECT @CustomerId = INSERTED.CustomerId
FROM INSERTED
INSERT INTO CustomerLogs
VALUES(@CustomerId, 'Inserted')
END
Update Trigger
In the below code is an example of an After Update Trigger. Now write the following code:
CREATE TRIGGER [dbo].[Customer_UPDATE]
ON [dbo].[Customers]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CustomerId INT
DECLARE @Action VARCHAR(50)
SELECT @CustomerId = INSERTED.CustomerId
FROM INSERTED
IF UPDATE(Name)
BEGIN
SET @Action = 'Updated Name'
END
IF UPDATE(Country)
BEGIN
SET @Action = 'Updated Country'
END
INSERT INTO CustomerLogs
VALUES(@CustomerId, @Action)
END
HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
