Oracle - Trigger I
[[99]
Published on: 10/05/2004
Triggers They are procedures that can be recorded in Java, PL / SQL or C. are executed (or fired) implicitly when a table is modified, an object is created or occur some user actions or database system.
Triggers are similar to the stored procedures differing, only, in the way they are called. The trigger is implicitly performed when a trigger event occurs while the stored procedure must be explicitly run.
A trigger is composed of four parts:
- Moment
- Event
- Type
Body
The time defines when a trigger will be triggered. It can be:
- Before (table)
- After (Table)
- Instead of (view)
Before Indicates that the trigger body PL / SQL commands will be run before the table data is changed. We usually use Before in the cases where we need to initiate global variables, validate business rules, change the flag value or to save the value of a column before we change their value. Example:
CREATE OR REPLACE TRIGGER novo_func
BEFORE...
.
.
.
END;
/
CREATE OR REPLACE TRIGGER novo_func
AFTER...
.
.
.
END;
/
Two very important details about instead of:
- only work with views and
- it's always line. It will be considered so, even if "for Each Row" is omitted.
Example:
CREATE OR REPLACE TRIGGER novo_func
INSTEAD OF INSERT ON vemp
FOR EACH ROW
WHEN ...
.
.
.
END;
/
- INSERT
Update
- Delete
When the event is an update We can inform which columns that , when they are changed, they will fire the trigger. The same does not occur with insert and delete because these instructions always affect the entire line. Example:
CREATE OR REPLACE TRIGGER novo_func
AFTER INSERT ON emp
.
.
.
END;
/
CREATE OR REPLACE TRIGGER novo_func
BEFORE INSERT OR UPDATE OR DELETE ON emp
.
.
.
END;
/
- Instruction (Statement)
- Line (ROW)
When the trigger is of the statement type it shall be triggered once For each trigger event, even if no line has been affected. They are useful for those trigger that eventually do not change data or for situations where what we want is a response from the trigger, for example, in a complex business restriction. By default all trigger is of this type. Example:
CREATE OR REPLACE TRIGGER novo_func
BEFORE INSERT OR UPDATE OR DELETE ON emp
FOR EACH STATEMENT
.
.
.
END;
/
Oracle - Trigger II
[/b]
99] Published on: 10/05/2004
When the trigger is the line type, the trigger will be performed every time the table is affected by the trigger event. If no row is affected to Trigger will not be executed. They are very useful when trigger action depends on the data affected by the trigger event. Example:
CREATE OR REPLACE TRIGGER novo_func
BEFORE INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
.
.
.
END;
/
As usually we need to work with the values ??before and after changing the data, the trigger allows us to refer to the values ??prior to the change (OLD) and after the change (new).
The name of a trigger must be unique within a same scheme, and its basic syntax is:
CREATE [OR REPLACE] TRIGGER [schema.] nome_da_trigger
[BEFORE|AFTER]
[DELETE|OR INSERT|OR UPDATE[OF coluna]]
ON [schema.] nome_da_tabela_ou_da_view
[REFERENCING [OLD [AS] OLD] [NEW [AS] NEW]
[FOR EACH ROW]
[WHEN [condição]]
Where :
Name_Rigger is the name of the trigger;
name_tabela_ou_da_view Indicates the table or view associated with the trigger;
Body_da_trigger is the action that Trigger will run. Starts by Declare or Begin and ends for END. You can also contain the call of a procedure.
Use the column name in the Update clause can increase performance because the trigger will only be triggered when that column specified in the clause is changed.
Now that we know how to create a trigger We will see a complete example:
First let's create a table to record a record of all users who connected to the bank :
CREATE TABLE vigia
(marca VARCHAR2(100));
CREATE OR REPLACE TRIGGER marca_logon
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO sys.vigia
VALUES (USER || ' entrou no sistema em ' ||
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
COMMIT;
END;
/
- After servererror
- After logon
- Before Logoff
- After startup
- Before Shutdown
You can create triggers using the above events for Database and Schema. The two exceptions are Shutdown and Startup that only apply to database. Example:
CREATE OR REPLACE TRIGGER marca_logoff
BEFORE LOGOFF ON SCHEMA
BEGIN
INSERT INTO sys.vigia
VALUES (USER || ' saiu do sistema em ' ||
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
COMMIT;
END;
/
CREATE OR REPLACE TRIGGER marca_logon
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO sys.vigia
VALUES (USER || ' entrou no sistema em ' ||
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
COMMIT;
END;
/
What is the error of this trigger? It is a very banal mistake, in case we stop closing the apostrophe (or simple quotes or quote) at the end of the to_char statement. When we run the error show it will show that there was a bug in line 4. This is because it points to where the line contains the error began to be written and not the line where it effectively occurred.
If you need more information about your trigger, View User_Triggers can provide very useful information. Example:
SELECT trigger_name
FROM user_triggers;
SELECT trigger_type, table_name, triggering_event
FROM user_triggers
WHERE trigger_name = 'nome_da_trigger';
SELECT trigger_name, trigger_type, triggering_event,
table_name, referencing_names,
status, trigger_body
FROM user_triggers
WHERE trigger_name = 'nome_da_trigger';
Oracle - Trigger III
Published: 10/05/2004 [99]
Eliminating Trigger:
DROP TRIGGER nome_da_trigger;
ALTER TRIGGER nome_da_trigger DISABLE;
ALTER TRIGGER nome_da_trigger ENABLE;
CREATE TABLE nova_emp
AS SELECT * FROM SCOTT.EMP;
CREATE OR REPLACE TRIGGER hora_exp
BEFORE INSERT ON nova_emp
BEGIN
IF (TO_CHAR(sysdate,'DY') IN ('SAB','DOM')) OR
(TO_CHAR(sysdate,'HH24:MI')
NOT BETWEEN '08:30' AND '17:30')
THEN RAISE_APPLICATION_ERROR (-20500,'Você só pode
atualizar os empregados no horário de
expediente');
END IF;
END;
/
CREATE OR REPLACE TRIGGER hora_exp
BEFORE INSERT OR UPDATE OR DELETE ON nova_emp
BEGIN
IF (TO_CHAR(sysdate,'DY') IN ('SAB','DOM')) OR
(TO_CHAR(sysdate,'HH24:MI')
NOT BETWEEN '08:30' AND '17:30')
THEN
IF DELETING THEN
RAISE_APPLICATION_ERROR (-20500,'Você só pode
excluir empregados no horário de expediente');
ELSIF INSERTING THEN
RAISE_APPLICATION_ERROR (-20502,'Você só pode
incluir empregados no horário de expediente');
ELSIF UPDATING ('SAL') THEN
RAISE_APPLICATION_ERROR (-20504,'Você só pode
alterar salarios no horário de expediente');
ELSE
RAISE_APPLICATION_ERROR (-20506,'Você só pode
Fazer alterações no horário de expediente');
END IF;
END IF;
END;
/
First let's create a table to contain the data of our history.
CREATE TABLE DDUR
(USUARIO VARCHAR2(15),
HORARIO DATE,
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2))
/
CREATE OR REPLACE TRIGGER hist_emp
AFTER INSERT OR UPDATE OR DELETE ON nova_emp
FOR EACH ROW
BEGIN
INSERT INTO ddur VALUES(
user, sysdate, :OLD.empno, :OLD.ename, :OLD.job,
:OLD.mgr, :OLD.hiredate, :OLD.sal, :OLD.comm, :OLD.deptno);
END;
/
CREATE OR REPLACE TRIGGER hist_emp
AFTER INSERT OR UPDATE OR DELETE ON nova_emp
FOR EACH ROW
BEGIN
INSERT INTO ddur VALUES(
user, sysdate, :NEW.empno, :NEW.ename, :OLD.JOB,
:NEW.MGR, :OLD.HIREDATE, :OLD.sal, :OLD.comm, :OLD.deptno);
END;
/
CREATE OR REPLACE TRIGGER aumento
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
IF (:NEW.sal - :OLD.sal) < :OLD.sal * 0.025
THEN
RAISE_APPLICATION_ERROR (-20512, 'Favor corrigir indice');
END IF;
END;
CREATE OR REPLACE TRIGGER ver_sal
BEFORE INSERT OR UPDATE OF sal, job
ON empl
FOR EACH ROW
WHEN (NEW.job_id <> 'PRESIDENT')
DECLARE
v_minsal emp.sal%TYPE;
v_maxsal emp.sal%TYPE;
BEGIN
SELECT MIN(sal), MAX(sal)
INTO v_minsal, v_maxsal
FROM emp
WHERE job = :NEW.job;
IF :NEW.sal < v_min OR
:NEW.sal > v_maxsal THEN
RAISE_APPLICATION_ERROR(-20515,'Salario inválido');
END IF;
END;
/
UPDATE emp
SET sal = 3400
WHERE ename = 'BLAKE';
A trigger can be much simpler than the above examples. For example, if we want to implement a restriction where the employee's salary can never be reduced, simply apply to trigger:
CREATE OR REPLACE TRIGGER veri_sal
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
WHEN (NEW.sal < OLD.sal)
BEGIN
RAISE_APPLICATION_ERROR (-20508,
'O salário não pode ser reduzido');
END;
/
How can we notice the trigger can be used in a very flexible manner. With them we can generate more flexible security mechanisms, audit data from tables and deploy business rules more easily.
Source:
Oracle - Trigger
[[99]
By: Milton Goya
E-mail: mgoya@ibta.com.br www.linhadecodigo.com