[Tip] Triggers

[Trevisolli]

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:

Select all

 
CREATE OR REPLACE TRIGGER novo_func 
	BEFORE... 
. 
. 
. 
END; 
/ 

After that the trigger body PL / SQL command will run after the table data is changed. Usually we use After to complete the data from other tables and to complete the activity of another time trigger. Example:

Select all

 
CREATE OR REPLACE TRIGGER novo_func 
	AFTER... 
. 
. 
. 
END; 
/ 

Instead of Indicates that the trigger will be performed instead of the instruction that triggered the trigger. Literally, the instruction is replaced by the trigger. This technique allows us to make, for example, changes in a table through a view. It is used in cases where the view can not change a table for not referring a column with the construct not null. In this case the trigger can update the column that the View does not have access.

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:

Select all

 
CREATE OR REPLACE TRIGGER novo_func 
	INSTEAD OF INSERT ON vemp 
FOR EACH ROW 
WHEN ... 
. 
. 
. 
END; 
/ 

The event defines the DML statement that triggers the trigger. It informs which SQL statement will fire the trigger. It may be:

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

Select all

 
CREATE OR REPLACE TRIGGER novo_func 
	AFTER INSERT ON emp 
. 
. 
. 
END; 
/ 

The event may contain one, two or all three DML operations on a single command line. Example:

Select all

 
CREATE OR REPLACE TRIGGER novo_func 
	BEFORE INSERT OR UPDATE OR DELETE ON emp 
. 
. 
. 
END; 
/ 

The type defines how many times a trigger will run. Trigger can be performed once for the instruction that triggered it or be fired for each line affected by the instruction that fired the trigger. It can be:

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

Select all

 
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:

Select all

 
CREATE OR REPLACE TRIGGER novo_func 
	BEFORE INSERT OR UPDATE OR DELETE ON emp 
	FOR EACH ROW 
. 
. 
. 
END; 
/ 

The body defines the action a trigger will run when triggered. The body of a trigger is composed of a PL / SQL block, the call of a procedure or by a Java procedure. By definition, the size of a trigger can not exceed 32K.

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:

Select all

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

PL / SQL block
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 :

Select all

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

Ready, we have our first trigger. It registers the name of the user and at what time it entered. This example was removed directly from Oracle documentation. In our example we reference to a system event instead of refering a table. Other system events are:

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

Select all

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

Eventually we can have some sort of error in our trigger. To check the compilation errors that we have in the trigger just use the show command show errors trigger_name_trigger. If you want to see the compilation errors of the last trigger you compiled you can write only show Errors or Sho Err. When we run this command it will show the line where the error is. Attention: If the line where the error is extended by more than one line, this command will indicate the beginning of the line. Let's create a trigger with error to serve as an example:

Select all

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

Trigger created with compilation error

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 all

 
SELECT trigger_name 
  FROM user_triggers; 

With the name of the trigger you want to analyze Run the command:

Select all

 
SELECT trigger_type, table_name, triggering_event 
  FROM user_triggers 
 WHERE trigger_name = 'nome_da_trigger'; 

or, if you need to get the code used to generate the trigger:

Select all

 
SELECT trigger_name, trigger_type, triggering_event, 
  table_name, referencing_names, 
  status, trigger_body 
  FROM user_triggers 
 WHERE trigger_name = 'nome_da_trigger'; 

If you find out that you no longer need Trigger there are two ways to treat the situation. Delete the trigger or disable it.



Oracle - Trigger III



Published: 10/05/2004 [99]
Eliminating Trigger:

Select all

 
 
DROP TRIGGER nome_da_trigger; 

If he prefers to only disable the trigger use the command:

Select all

 
ALTER TRIGGER nome_da_trigger DISABLE;  

99]] When the trigger is created for the first time it is automatically enabled. To enable the trigger again just use the command:

Select all

 
 
ALTER TRIGGER nome_da_trigger ENABLE;  

But let's continue creating our triggers. The next case will help us prevent someone from registering an employee outside the hours of office hours:

Select all

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

This trigger can be refined to test conditional predicates. For example:

Select all

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

Let's see how to use Old and New values:
First let's create a table to contain the data of our history.

Select all

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

Now let's create our trigger. She must register everything we do in our table.

Select all

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

The reference: OLD indicates that we are using the values ??prior to change. If we wanted to use the updated value the reference would be: New. Both can be used in the same trigger. For example, our trigger could have been written like this:

Select all

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

You can use: OLD E: NEW in comparisons inside your trigger, riding increasingly complex PL / SQL structures . For example:

Select all

 
 
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; 

In the case above, if the salary increase is less than 2.5%, the system warns that there was an error in the salary alteration. In another example, but now with when

Select all

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

in this case we are guaranteed that no one who is hired with the different position of President will receive a smaller salary than The lowest salary of his position or a salary greater than the largest salary of his position.

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:

Select all

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

so that a user creates his own triggers he needs to have the CREATE trigger system privilege and be the owner of the table where you will create the trigger. If not owner it must have the Alter or Alter Any Table system privilege. If you need to create triggers for database events must have the Database Trigger Administer privilege. If the trigger makes a call of some procedure, whoever is creating the trigger must have the execute privilege in the procedure.

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