oracle trigger - ghdrako/doc_snipets GitHub Wiki

ALTER TRIGGER trigger_name DISABLE;
ALTER TRIGGER trigger_name ENABLE;

Before

CREATE OR REPLACE TRIGGER student_bi
BEFORE INSERT ON STUDENT
FOR EACH ROW
BEGIN
:NEW.student_id := STUDENT_ID_SEQ.NEXTVAL;
:NEW.created_by := USER;
:NEW.created_date := SYSDATE;
:NEW.modified_by := USER;
:NEW.modified_date := SYSDATE;
END;

In the body of the trigger, there is a pseudorecord, :NEW, which allows for accessing a row that is currently being processed. In other words, a row that is inserted in the STUDENT table. The :NEW pseudorecord is of the type TRIGGERING_TABLE%TYPE, so, in this case, it is of the STUDENT %TYPE type.

In addition to the :NEW pseudorecord, an :OLD pseudorecord exists. It allows you to access the current information of the record that is being updated or deleted. Thus, the :OLD pseudorecord is undefined for the INSERT statements, and the :NEW pseudorecord is undefined for the DELETE statements. However, the PL/ SQL compiler does not generate syntax errors when :OLD or :NEW pseudorecords are used in triggers where the triggering event is an INSERT or DELETE operation, respectively. In this case, the attribute values are set to NULL for the :OLD and :NEW pseudorecords.

After

create or replace trigger trg_mrs_debet_przekladka
after insert or update on mrs_debet_przekladka 
for each row
declare
    --v_row mrs_debet_przekladka%rowtype;
    --v_hist mrs_debet_przekladka_hist%rowtype;
begin
insert into mrs_debet_przekladka_hist (
  nr_karty_zastrz
  ,nr_karty        
  ,numrach         
  ,dt_ins          
  ,dt_upd )         
values (
   :new.nr_karty_zastrz
  ,:new.nr_karty
  ,:new.numrach
  ,:new.dt_ins
  ,:new.dt_upd);
end;

CREATE TABLE AUDIT_TRAIL
(TABLE_NAME VARCHAR2(30)
,TRANSACTION_NAME VARCHAR2(15)
 ,TRANSACTION_USER VARCHAR2(30)
,TRANSACTION_DATE DATE DEFAULT SYSDATE);
CREATE OR REPLACE TRIGGER instructor_aud
AFTER UPDATE OR DELETE ON INSTRUCTOR
DECLARE
v_trans_type VARCHAR2(10);
BEGIN
v_trans_type := CASE
WHEN UPDATING THEN 'UPDATE'
WHEN DELETING THEN 'DELETE'
END;
INSERT INTO audit_trail
(TABLE_NAME, TRANSACTION_NAME, TRANSACTION_USER)
VALUES
('INSTRUCTOR', v_trans_type, USER);
END;

The body of the trigger contains two Boolean functions: UPDATING and DELETING. The UPDATING function evaluates to TRUE if an UPDATE statement is issued on the table, and the DELETING function evaluates to TRUE if a DELETE statement is issued on the table. Another Boolean function, INSERTING, also evaluates to TRUE when an INSERT statement is issued against the table. This trigger inserts a record into the AUDIT_TRAIL table when an UPDATE or DELETE operation is issued against the INSTRUCTOR table.

Autonomous Transaction

when a trigger fires, all operations performed by the trigger become part of a transaction. When this transaction is committed or rolled back, the operations performed by the trigger also are committed or rolled back.

An autonomous transaction is an independent transaction started by another transaction that is usually referred to as the main transaction. In other words, an autonomous transaction may issue various DML statements and commit or roll them back, without committing or rolling back the DML statements issued by the main transaction.

CREATE OR REPLACE TRIGGER instructor_aud
AFTER UPDATE OR DELETE ON INSTRUCTOR
DECLARE
v_trans_type VARCHAR2(10);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
v_trans_type := CASE
WHEN UPDATING THEN 'UPDATE'
WHEN DELETING THEN 'DELETE'
END;
INSERT INTO audit_trail
(TABLE_NAME, TRANSACTION_NAME, TRANSACTION_USER)
 VALUES
('INSTRUCTOR', v_trans_type, USER);
COMMIT;
END;

Even though the changes on the INSTRUCTOR table are rolled back, the AUDIT_TRAIL table will continue to contain a record of the attempted UPDATE operation.