can anyone post some simple examples of triggers and how they work?

Dear Shreesh,

Please follow below link
http://www.dotnetfunda.com/articles/article724-triggers-in-sql.aspx

Thanks

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

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

Hi Shreesh,

You can follow the below link:
http://www.sqlteam.com/article/an-introduction-to-triggers-part-i

Thanks,
Arefin

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

Check this out

http://msdn.microsoft.com/en-us/library/aa258254%28v=sql.80%29.aspx

Best Regards,
Niladri Biswas

Shreesh, 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;



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.

Shreesh, 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;

/

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.

Thanks & Regards
Hari

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

Example 1
/* Create the 10 digit style member_number */
CREATE TRIGGER create_member_number ON [dbo].[member]
FOR INSERT AS
UPDATE member SET member_number = '123' + RIGHT('0000000' + CAST(member_id AS varchar), 7)

Example 2

CREATE TRIGGER MyTableUpdate
ON dbo.MyTable
FOR update
AS
UPDATE
MyTable
SET last_updated = GetDate()
From MyTable Inner Join Inserted On
MyTable.KeyField1 = Inserted.KeyField1
and MyTable.KeyField2 = Inserted.KeyField2
and MyTable.KeyField3 = Inserted.KeyField3

Shreesh, 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...

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

Please Mark as Answer if u satisfied for this answer

Thanks & Regards
Hari

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