| Trigger | Stored Procedure |
|---|---|
| Trigger is an act which is performed automatically before or after an event has occurred. | Stored procedure is a set of functionality which is executed when it is explicitly invoked. |
| It cannot accept parameters. | It can accept parameters. |
| A trigger cannot return any value. | A stored procedure can return a value. |
| It is executed automatically on some event. | It needs to be explicitly called. |
| Triggers are used for insertion, update and deletion. | Stored procedures are often used independently in the database. |
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)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
| Id | Name | Designation | Salary |
|---|---|---|---|
| 1 | Albert | Programmer | 50000 |
| 2 | Anna | HR | 25000 |
| 3 | Mark | Analyst | 55000 |
| 4 | Jason | Content writer | 20000 |
| 5 | Andrew | Programmer | 90000 |
CREATE OR REPLACE TRIGGER print_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON employees
FOR EACH ROW
WHEN (NEW.Id>0)
DECLARE
sal_diff NUMBER;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
DBMS_OUTPUT.PUT(:NEW.last_name || ': ');
DBMS_OUTPUT.PUT('Old salary = ' || :OLD.salary || ', ');
DBMS_OUTPUT.PUT('New salary = ' || :NEW.salary || ', ');
DBMS_OUTPUT.PUT_LINE('Difference: ' || sal_diff);
END;
/
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 1000;
IF sql%notfound THEN
dbms_output.put_line('no employee updated');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' employee updated ');
END IF;
END;
/