Search content within the blog

Friday, April 17, 2009

Triggers in SQLServer

Triggers are a special class of stored procedure defined to execute automatically when an UPDATE, INSERT, or DELETE statement is issued against a table or view. Triggers are powerful tools that sites can use to enforce their business rules automatically when data is modified.

The CREATE TRIGGER statement can be defined with the FOR UPDATE, FOR INSERT, or FOR DELETE clauses to target a trigger to a specific class of data modification actions. When FOR UPDATE is specified, the IF UPDATE (column_name) clause can be used to target a trigger to updates affecting a particular column.
You can use the FOR clause to specify when a trigger is executed:

* AFTER (default) - The trigger executes after the statement that triggered it completes. If the statement fails with an error, such as a constraint violation or syntax error, the trigger is not executed. AFTER triggers cannot be specified for views.Multiple after triggers can be specified on a table or view.

* INSTEAD OF -The trigger executes in place of the triggering action. INSTEAD OF triggers can be specified on both tables and views.That is if you write an instead of trigger for insert on some table then the any insert statement on that table wont work instead the any statement within the trigger will get executed. instead of triggers are mostly used in conjuection with views to update data .


Instead of triggers example:
CREATE TABLE BaseTable
(
PrimaryKey int IDENTITY(1,1),
Color nvarchar(10) NOT NULL,
Material nvarchar(10) NOT NULL,
ComputedCol AS (Color + Material)
)
GO

insert into basetable (Color,Material) select('green','Matgrn')
insert into basetable (Color,Material) select('Red','Matred')
insert into basetable (Color,Material) select('White','Matwht')
insert into basetable (Color,Material) select('Orange','Matorg')
insert into basetable (Color,Material) select('Violet','Matviolet')

select * from BaseTable

--Create a view that contains all columns from the base table.
CREATE VIEW InsteadView
AS SELECT PrimaryKey, Color, Material, ComputedCol
FROM BaseTable
GO

select * from InsteadView

Create TRIGGER InsteadTrigger on InsteadView
INSTEAD OF INSERT
AS
BEGIN
--Build an INSERT statement ignoring inserrted.PrimaryKey and
--inserted.ComputedCol.
INSERT INTO BaseTable
SELECT Color+'altered', Material+'altered'
FROM inserted
END

now when you say the following statement
insert into InsteadView values (1,'ash','win',1)

the trigger gets executed and you can check the result using
select * from BaseTable
or
select * from InsteadView



After trigger example

CREATE TRIGGER [trglogin]
ON [dbo].[tbl_Login]
FOR UPDATE
AS
BEGIN
if((UPDATE(col1))
begin
print 'col1 is updated..'
end
if((UPDATE(col2))
begin
print 'col2 is updated..'
end
end

After trigger (delete)
DELETE triggers are used for restricting the data that your users can remove from a database.

CREATE TRIGGER DelhiDel ON [Customers]
FOR DELETE
AS
IF (SELECT state FROM deleted) = 'Delhi'
BEGIN
PRINT 'Can not remove customers from Delhi'
PRINT 'Transaction has been canceled'
ROOLBACK
END

No comments:

Post a Comment