|
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
|