Triggers are procedures that are stored in the database and are implicitly run, or fired, when something happens.

Traditionally, triggers supported the execution of a PL/SQL block when an INSERT, UPDATE, or DELETE occurred on a table or view.

Unlike a procedure, or a function, which must be invoked explicitly, database triggers are invoked implicitly.
Use the following guidelines when designing your triggers:
Use triggers to guarantee that when a specific operation is performed, related actions are performed.
Do not define triggers that duplicate features already built into Oracle Database.
For example, do not define triggers to reject bad data if you can do the same checking through declarative integrity constraints.
Limit the size of triggers. If the logic for your trigger requires much more than 60 lines of PL/SQL code, it is better to include most of the code in a stored procedure and call the procedure from the trigger.
Database triggers can be used to perform any of the following:
Audit data modification

Log events transparently

Enforce complex business rules

Maintain replicate tables
CREATE TABLE EMP
AS SELECT EMPLOYEE_ID, First_Name, salary
from EMPLOYEES ;

CREATE OR REPLACE TRIGGER EMP_Sal_print
BEFORE UPDATE OF SALARY
ON EMP
FOR EACH ROW
DECLARE SAL_DIFF number:= 0;
BEGIN
SAL_DIFF := :NEW.SALARY - :OLD.SALARY;
DBMS_OUTPUT.PUT_LINE ('The old Salary was: '||:OLD.SALARY);
DBMS_OUTPUT.PUT_LINE ('The NEW Salary IS: '||:NEW.SALARY);
DBMS_OUTPUT.PUT_LINE ('The Salary Difference is IS: '||SAL_DIFF);

END;

SELECT * FROM EMP;

UPDATE EMP
SET SALARY = 10000
WHERE EMPLOYEE_ID = 104;


create table emp_audit
(
employee_id number,
Old_Salary number,
new_SAlary number,
userName varchar(30),
ModificationDate date
);

create or REPLACE TRIGGER EMP_AUDIT_TRIGG
AFTER UPDATE OF SALARY ON EMP
FOR EACH ROW
BEGIN
INSERT INTO EMP_Audit(employee_id, Old_Salary, new_SAlary, userName, ModificationDate)
values (:old.employee_id, :old.salary, :new.salary, USER, SYSDATE);
END;

SELECT * FROM EMP;

UPDATE EMP
SET SALARY = 7500
WHERE EMPLOYEE_ID = 107;

SELECT * FROM EMP_Audit;

CREATE OR REPLACE TRIGGER EMP_SECURE_TRIGGER
BEFORE INSERT OR UPDATE OR DELETE
ON EMP
BEGIN
IF TO_CHAR(SYSDATE, 'DAY') IN ('SUNDAY', 'SATURDAY')
OR TO_CHAR(SYSDATE, 'hh:mi') NOT BETWEEN '12:00' AND '15:00'
THEN
RAISE_APPLICATION_ERROR(-20000, 'You are not allowed during Sat or sun or between 12 to 3 pm');
end IF;
END;

INSERT INTO EMP VALUES(1000, 'Rafay', 343434);

свернуть