Triggers

[caradoc]

Why do one go for triggers concept when we have procedures which does the same functionality.Someone provide me idea on this.

[gpraveen_mca]

Triggers are events that are executed whenever an insert or delete or update happens. Basically, triggers are events and the code associated with triggers is executed automatically when the event occurs. These events are purely database events.

Procedures on other hand, should be run explicitily by the user either from database or from user applications.

[Adrian]

Hi All,
Nice discussion going around. Can you kindly explain the same with an example so that I can get even more clearer view of the concept.

[neelu.payola]

the main difference is that Trigger activates automatically if and when condition is meet within the database transaction. It is like a rule in the database whenever a transaction is done and condition is meet.

And

Stored Procedure have to be activated or triggered or called by something in order to activate.



A trigger is generally used to enforce business rules in a database, such as a rental property needs to have an associated landlord in a realestate database, although it can be used to enforce data types or structures as well. for instance:

Code:
create or replace trigger unique_pk before insert or update on table
declare
unique : number;
before each row
begin
select count(*) into unique from table where pk = :new.pk;
if (unique > 0) then
raise_error(-20001,"Primary key allready exists");
end if;
end;

A procedure is something used to make one or more (advanced?) sql queries into a nice little function, for instance:

Code:
create procedure add_rental_property(address : varchar2 etc etc)
insert into landlords lastname, firstname etc etc
insert into property address, rent etc etc
insert into tenant_property null, null etc etc
end
then all you have to do is exec add_rental property("6 elm st.","Freddy","Krueger",etc etc); to do all those sql statements