What is triggers?types of triggres with examples

Posted by Sheeba under ASP.NET on 11/27/2010 | Points: 10 | Views : 5130 | Status : [Member] | Replies : 9
Hi

What is triggers ? types of triggres with examples

Thank you




Responses

Posted by: SheoNarayan on: 11/27/2010 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
A trigger is a database object like Stored procedure that executes automatically on certain action specified on a table. For example, if a record will be inserted on table, you can set a trigger to execute.

For more details about the triggers, visit following links

http://www.dotnetfunda.com/articles/article724-triggers-in-sql.aspx
http://www.dotnetfunda.com/interview/exam47-how-many-types-of-triggers-are-there-.aspx

Hope this will help.

Thanks

Regards,
Sheo Narayan
http://www.dotnetfunda.com

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

Posted by: T.saravanan on: 11/29/2010 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Sheeba,

Refer those links...

http://www.sql-server-performance.com/articles/audit/ddl_triggers_p1.aspx

http://msdn.microsoft.com/en-us/library/ms189799.aspx

Thanks,
T.Saravanan

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

Posted by: Lalji_mer on: 11/29/2010 [Member] Bronze | Points: 25

Up
0
Down

-->A Trigger is a block of code,fired whenever data in the underlying table is affected by any of the Data Manipulation Language (DML) statements -INSERT,UPDATE,OR DELETE.

whenever trigger fires,two special tables are created-
1.insert table 2.Delete table.

1.inserted table contains copy of all records that are insetred in the trigger table.
2.deleted table contains all records that have been deleted from trigger table.this table is used to refer old values.

Whenever any updation takes place,the trigger uses both the inserted and deleted tables.

Type of Triggers
http://www.dotnetspark.com/kb/86-triggers.aspx
http://www.codeproject.com/KB/database/TriggersSqlServer.aspx

LP MER

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

Posted by: PandianS on: 11/30/2010 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

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

Please refer BOL for further deep drive.....

Cheers


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

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

Posted by: SheoNarayan on: 11/30/2010 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
Very well written PandianS! Keep it up!

Regards,
Sheo Narayan
http://www.dotnetfunda.com

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

Posted by: Gokul on: 12/8/2010 [Member] Starter | Points: 25

Up
0
Down
Good work Pandy. Continue doing the gud work.

regards
Gokul.N

Thanks and Regards,
GokulNath Nithy.

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

Posted by: Muhsinathk on: 9/13/2012 [Member] Bronze | Points: 25

Up
0
Down
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.

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

Posted by: Muhsinathk on: 9/13/2012 [Member] Bronze | Points: 25

Up
0
Down
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;

/

3) Lets update the price of a product.

UPDATE PRODUCT SET unit_price = 800 WHERE product_id = 100

Once the above update query is executed, the trigger fires and updates the 'product_price_history' table.

4)If you ROLLBACK the transaction before committing to the database, the data inserted to the table is also rolled back.

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

Posted by: Muhsinathk on: 9/14/2012 [Member] Bronze | Points: 25

Up
0
Down
Hi Sheeba,
Please Mark as Answer if it helpful to you..That helps others who search the same...

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

Login to post response