Triggers and types of triggers

Triggers
Triggers are the group of SQL statements which we can say that it helps to add functionality to our tables so that the tables can perform a certain actions when some queries are executed.

Difference
Stored procedures live in the database as a compiled collection of SQL statements. We can say that a stored procedure can access and modify the data present in many tables in the database. Also, the important thing is that a stored procedure is not associated with any particular database object. Whereas triggers are special procedures which are attached to a specific database object for example, a table. One more important differences is that stored procedures have to be called explicitly by the user to run it, But in case of triggers it get executed when the particular event associated with the event gets fired.

G. S.
.Net Developer

Rajasekhar0544, if this helps please login to Mark As Answer. | Alert Moderator

Stored procedures will get compiled only once,so execution is faster.
triggers will get compiled each time when application runs.

Differences between storedprocedures and triggers
1) We can execute a stored procedure whenever we want with the help of the exec command, but a trigger can only be executed whenever an event (insert, delete and update) is fired on the table on which the trigger is defined.

3) Stored procedures can be scheduled through a job to execute on a predefined time, but we can't schedule a trigger.

4) Stored procedure can take the input parameters, but we can't pass the parameters as an input to a trigger.

5) Stored procedures can return values but a trigger cannot return a value.

6) We can use the Print commands inside the stored procedure to debug purpose but we can't use the print command inside a trigger.

7) We can use the transaction statements like begin transaction, commit transaction and rollback inside a stored procedure but we can't use the transaction statements inside a trigger.

8) We can call a stored procedure from front end (.asp files, .aspx files, .ascx files etc.) but we can't call a trigger from these files.


Refer this link

http://www.codeproject.com/Articles/25600/Triggers-Sql-Server

mahesh

Rajasekhar0544, if this helps please login to Mark As Answer. | Alert Moderator

Triggers Information:
a)While we inserting/update/delete values in table,same action need to perform in another table also at same time.
so v choosing triggers..
b)Benefits:
b1)Faster application developement(coz database stores triggers,u dont have to code the trigger actions into each databse application)
b2)Global enforcement of business rules(define trigger once and reuse it for any application)
b3)easier maintenance(If business policy changes,u need to change oly corresponding trigger program instead of each appln pgm)

c)Disadvantages:
c1)its easyto view table relationship,constraints,indexes and Sp in database.But triggers are difficult to view
c2)Trigers run every time when Database fields are updated/insert/delete and it it overhead on system.So it makes system slower.
c3)Triggers hide database operations

d)Diff between Triiger and SP

Trigger
d1)no need to call triggers.fire automatically
d2)implicitly executed
d3)dont pass parameters


SP
d1) have to call SP
d2)explicitly executed by invoking call to SP
d3)pass parameters

Rajasekhar0544, if this helps please login to Mark As Answer. | Alert Moderator

A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.

Syntax of Triggers

The Syntax for creating a trigger is:

CREATE [OR REPLACE ] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF }

{INSERT [OR] | UPDATE [OR] | DELETE}

[OF col_name]

ON table_name

[REFERENCING OLD AS o NEW AS n]

[FOR EACH ROW]

WHEN (condition)

BEGIN

--- sql statements

END;

SQL Server 2000 has many types of triggers:

1. After Trigger
2. Multiple After Triggers
3.Instead Of Triggers
4. Mixing Triggers Type

Differences between storedprocedures and triggers
1. When you create a trigger you have to identify event and action of your trigger but when you create s.p you don't identify event and action

2.Trigger is run automatically if the event is occured but s.p don't run automatically but you have to run it manually

3. Within a trigger you can call specific s.p but within a sp you cannot call atrigger

4.Trigger execute implicitly whereas store procedure execute via procedure call from another block.

5.We can call a stored procedure from front end (.asp files, .aspx files, .ascx files etc.) but we can't call a trigger from these files.

6. Stored procedure can take the input parameters, but we can't pass the parameters as an input to a trigger.

Rajasekhar0544, if this helps please login to Mark As Answer. | Alert Moderator

We can create a trigger to update the 'product_price_history' table when the price of the product is updated in the 'product' table.

1) Create the 'product' table and 'product_price_history' table

CREATE TABLE product_price_history

(product_id number(5),

product_name varchar2(32),

supplier_name varchar2(32),

unit_price number(7,2) );


CREATE TABLE product

(product_id number(5),

product_name varchar2(32),

supplier_name varchar2(32),

unit_price number(7,2) );

2) Create the price_history_trigger and execute it.

CREATE or REPLACE TRIGGER price_history_trigger

BEFORE UPDATE OF unit_price

ON product

FOR EACH ROW

BEGIN

INSERT INTO product_price_history

VALUES

(:old.product_id,

:old.product_name,

:old.supplier_name,

:old.unit_price);

END;

Rajasekhar0544, if this helps please login to Mark As Answer. | Alert Moderator

Hi Rajasekhar,

We have THREE types of triggers as given below

1.DML Trigger
- For/After/Instead Of (Used in Table)
- Instead Of (Used in View)
These are the events raised when INSERT, UPDATE, DELETE activity performed on Tables / Views

2.DDL Trigger
- Database Scope Events
- Server Scope Events

For Instance, These are the events raised when DDL activity performed( i.e: CREATE_TABLE, ALTER_TABLE, DROP_TABLE,....Etc)

3.Logon Trigger
Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server

Thanks & Regards
Hari

Rajasekhar0544, if this helps please login to Mark As Answer. | Alert Moderator

1)After Triggers
1. Working with INSERT Triggers

INSERT INTO Customers
VALUES ('Mayank','Gupta','Hauz Khas','Delhi',
'Delhi','110016','01126853138')
INSERT INTO Customers
VALUES('Himanshu','Khatri','ShahjahanMahal ',
'Jaipur','Rajesthan','326541','9412658745')
INSERT INTO Customers
VALUES ('Sarfaraz','Khan','Green Market',
'Hydrabad','AP','698542','9865478521')

INSERT INTO Products
VALUES ('ASP.Net Microsoft Press',550)
INSERT INTO Products
VALUES ('ASP.Net Wrox Publication',435)
INSERT INTO Products
VALUES ('ASP.Net Unleased',320)
INSERT INTO Products
VALUES ('ASP.Net aPress',450)

CREATE TRIGGER invUpdate ON [Orders]
FOR INSERT
AS
UPDATE p SET p.instock=[p.instock - i.qty]
FROM products p JOIN inserted I ON p.prodid = i.prodid
2.Working with DELETE Triggers
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
3.Working with UPDATE Triggers

CREATE TRIGGER CheckStock ON [Products]
FOR UPDATE
AS
IF (SELECT InStock FROM inserted) < 0
BEGIN
PRINT 'Cannot oversell Products'
PRINT 'Transaction has been cancelled'
ROLLBACK
END
2.Multiple After Triggers
If you are using multiple triggers, it is of course essential to know which order they fire in.If you want more than two triggers to fire in a specific order, there is no way to specifically define this. A deeply unscientific test I did indicated that multiple triggers for the same table and operation will run in the order they were created unless you specifically tell them otherwise.

3.Instead Of Triggers
Instead Of Triggers fire instead of the operation that fires the trigger, so if you define an Instead Of trigger on a table for the Delete operation, they try to delete rows, they will not actually get deleted (unless you issue another delete instruction from within the trigger) as in this simple example:

CREATE TABLE Mayank (Name varchar(32))
GO

CREATE TRIGGER tr_mayank ON Mayank
INSTEAD OF DELETE
AS
PRINT 'Sorry - you cannot delete this data'
GO

INSERT Mayank
SELECT 'Cannot' union
SELECT 'Delete' union
SELECT 'Me'
GO

DELETE Mayank
GO

SELECT * FROM Mayank
GO
DROP TABLE Mayank

1) You can define an Instead Of trigger on a view (something that will not work with After triggers) and this is the basis of the Distributed Partitioned Views that are used so split data across a cluster of SQL Servers.
2) You can use Instead Of triggers to simplify the process of updating multiple tables for application developers.
3)Mixing Trigger Types.

Rajasekhar0544, if this helps please login to Mark As Answer. | Alert Moderator

Please Mark as Answer if it helpful to you..That helps others who search the same...

Rajasekhar0544, if this helps please login to Mark As Answer. | Alert Moderator

Please Mark as Answer if u satisfied for this answer

Thanks & Regards
Hari

Rajasekhar0544, if this helps please login to Mark As Answer. | Alert Moderator