SQL Server code example: Block UPDATEs to a table using INSTEAD TRIGGER

=========================
General
=========================
Block all updates to a table via INSTEAD OF Triggers.

use my_database
GO
CREATE TRIGGER SitesServiceManager_TG01 ON user.SitesServiceManager
INSTEAD OF UPDATE AS
BEGIN
  RETURN
END

use my_database
GO
CREATE TRIGGER SitesServiceManager_TG02 ON user.SitesServiceManager
INSTEAD OF DELETE AS
BEGIN
  RETURN
END

GO

use my_database
GO
CREATE TRIGGER SitesServiceManager_TG03 ON user.SitesServiceManager
INSTEAD OF INSERT AS
BEGIN
  RETURN
END

GO


Procedures to Enable/Disable triggers:
CREATE PROCEDURE [dbo].[block_updates_on_SitesServiceManager]  AS
BEGIN
  ALTER TABLE user.SitesServiceManager ENABLE TRIGGER SitesServiceManager_TG01 
  ALTER TABLE user.SitesServiceManager ENABLE TRIGGER SitesServiceManager_TG02
  ALTER TABLE user.SitesServiceManager ENABLE TRIGGER SitesServiceManager_TG03 
END

CREATE PROCEDURE [dbo].[allow_updates_on_SitesServiceManager]  AS

BEGIN

  ALTER TABLE user.SitesServiceManager DISABLE TRIGGER SitesServiceManager_TG01 

  ALTER TABLE user.SitesServiceManager DISABLE TRIGGER SitesServiceManager_TG02

  ALTER TABLE user.SitesServiceManager DISABLE TRIGGER SitesServiceManager_TG03

END

Execute procedures to Enable/Disable triggers:

[my_database].[dbo].[allow_updates_on_SitesServiceManager] 

[my_database].[dbo].[block_updates_on_SitesServiceManager] 

Audit Table changes with Trigger by example

==================================
General
==================================
This is an example of auditing DML operations on a table using a trigger, and logging these changes into an audit table.
Audit table resides on a dedicated Tablespace.
Optionally this table can be partitioned by change date.

==================================
Trigger code
==================================
The trigger is inserting records into Audit table whenever there is an update on the base table.

CREATE OR REPLACE TRIGGER AUD_CUSTOMERS_TRG
  AFTER UPDATE OR INSERT OR DELETE ON CUSTOMERS
  FOR EACH ROW
DECLARE
  v_oper_user VARCHAR2(30);
BEGIN

  SELECT  UPPER(SUBSTR(SYS_CONTEXT('USERENV','OS_USER'),1,30)) INTO v_oper_user 
    FROM DUAL;

  IF INSERTING THEN
    INSERT INTO AUD_CUSTOMERS
      (change_date, change_action, old_new, customer_id, name, oper_user)
    VALUES
      (SYSDATE, 'INSERT', 'NEW', :NEW.customer_id, :NEW.name, v_oper_user);
  ELSIF UPDATING THEN
    INSERT INTO AUD_CUSTOMERS
      (change_date, change_action, old_new, customer_id, name, oper_user)
    VALUES
      (SYSDATE, 'UPDATE', 'OLD', :OLD.customer_id, :OLD.name,  v_oper_user);
    INSERT INTO AUD_CUSTOMERS
      (change_date, change_action, old_new, customer_id, name, oper_user)
    VALUES
      (SYSDATE, 'UPDATE', 'NEW', :NEW.customer_id, :NEW.name, v_oper_user);
  ELSE
    INSERT INTO AUD_CUSTOMERS
      (change_date, change_action, old_new, customer_id, name, oper_user)
    VALUES
      (SYSDATE, 'DELETE', 'OLD', :OLD.customer_id, :OLD.name,  v_oper_user);
  END IF;
END;

==================================

The DDL of the Audit Table

==================================

The structure of the Audit table is same as the base table, with addition of four fields:

- change_date

- change_action: INSERT/UPDATE/DELETE

- old_new: NEW/OLD

- oper_user: The OS login of the user who made this change.

CREATE TABLE AUD_CUSTOMERS(

change_date                DATE NOT NULL, 

change_action              VARCHAR2(10 BYTE) NOT NULL,

old_new                    VARCHAR2(3 BYTE) NOT NULL,

customer_id                VARCHAR2(5 BYTE)  ,                          

name                       VARCHAR2(30 BYTE) ,                          

oper_user                  VARCHAR2(30 BYTE)  

)

tablespace AUDIT_TBS NOLOGGING;

alter table AUD_CUSTOMERS

  add constraint AUD_CUSTOMERS_PK primary key (change_date, change_action, old_new)

  using index 

  tablespace AUDIT_TBS;

grant select, insert, update, delete, references, alter, index on AUD_CUSTOMERS to MANAGER;

grant select on AUD_CUSTOMERS to SELECTOR;

grant select on AUD_CUSTOMERS to USERS_ROLE;

PL/SQL Reference VIII - Triggers

===========================================
Triggers Improvement in Oracle 11G
===========================================
Compound Triggers
Disabled Triggers
FOLLOWS clause


===========================================
Compound Triggers
===========================================
Compound Trigger has four timing points:
- BEFORE STATEMENT - before firing the statement
- BEFORE ROW - before row affect
- AFTER ROW - after row affect
- AFTER STATEMENT - after firing the statement

Syntax
CREATE OR REPLACE TRIGGER my_trigger
  FOR DML_event ON my_table
  COMPOUND TRIGGER
  --declarations
  //declarations

  --BEFORE STATEMENT
  BEFORE STATEMENT IS
  BEGIN
    //actions
  END BEFORE STATEMENT;

  --BEFORE EACH ROW
  BEFORE EACH ROW IS
  BEGIN
    //actions
  END BEFORE EACH ROW;

  --AFTER EACH ROW
  AFTER EACH ROW IS
  BEGIN
    //actions
  END AFTER EACH ROW;

  --AFTER STATEMENT

  AFTER STATEMENT IS
  BEGIN
    //actions
  END AFTER STATEMENT;

END my_trigger


Example
Example of using a compound trigger to log changes to ORDERS table to ORDERS_AUD.

CREATE OR REPLACE TRIGGER orders_aud_trg
  FOR INSERT or UPDATE ON ORDERS
  COMPOUND TRIGGER
  --declarations
  limit_const CONSTANT PLS_INTEGER :=7;
  
  TYPE order_totals_type IS TABLE OF orders_aud%ROWTYPE INDEX BY PLS_INTEGER;
  v_order_totals order_totals_type;
  v_counter PLS_INTEGER :=0;
  
  PROCEDURE flush_array_proc IS
    count_elements PLS_INTEGER := v_order_totals.count();
  BEGIN
    FORALL j IN 1..count_elements 
      INSERT INTO ORDERS_AUD VALUES v_order_totals(j);

    v_order_totals.delete();
    v_counter =0;
  END flush_array_proc ;

  --BEFORE STATEMENT
  BEFORE STATEMENT IS
  BEGIN
    v_order_totals.delete();
    v_counter := 0;
  END BEFORE STATEMENT;

  --AFTER EACH ROW

  AFTER EACH ROW IS
  BEGIN
    v_counter := v_counter + 1;
    v_order_totals(v_counter).order_id := :NEW.order_id;
    v_order_totals(v_counter).run_date := SYSDATE;
    v_order_totals(v_counter).user_name := SYS_CONTEXT('userenv',session_user');
    v_order_totals(v_counter).old_total := OLD.order_total;
    v_order_totals(v_counter).new_total := NEW.order_total;
    IF v_order_totals >= limit_const THEN
      flush_array_proc();
    END IF;
  END AFTER EACH ROW;

  --AFTER STATEMENT  

  AFTER STATEMENT IS
  BEGIN
    flush_array_proc();
  END AFTER STATEMENT;

END orders_aud_trg;

===========================================
Disabled Triggers
===========================================
New from Oracle 11.
it is possible to create a trigger in DISABLE state.
This can be useful in case a trigger was created, but cannot be compiled.
Before - The trigger failed at runtime, and the DML to the table failed.
Now - The trigger can be Disabled/Enabled as needed.

CREATE OR REPLACE TRIGGER my_trigger BEFORE INSERT ON some_table FOR EACH ROW DISABLE
BEGIN
  //do something
END;
/

===========================================
FOLLOWS clause
===========================================

Allows to order the execution of triggers relative one to another.

CREATE OR REPLACE TRIGGER my_trigger AFTER UPDATE OF some_column ON some_table FOR EACH ROW FOLLOWS my_another_trigger
BEGIN
  //do something
END;
/

Triggers in Oracle by Example

=============================

How to see on which table there are triggers?
=============================

SELECT owner, trigger_name, triggering_event, trigger_body

 FROM DBA_TRIGGERS 

WHERE table_name  like '%MY_TABLE%';

=============================
How to see trigger body?
=============================

Trigger body defined as LONG datatype in USER_TRIGGERS.

SET LONG 100000
SPOOL terigger_text.sql
SELECT description, TRIGGER_BODY  
 FROM USER_TRIGGERS 
WHERE trigger_name  like '%LSM_TEXT%';
SPOOL OFF

=============================
How to see all triggers on a Table
=============================
SELECT * 
FROM  ALL_SOURCE 
WHERE type = 'TRIGGER' 
  AND text like '%MY_TABLE%';

=============================
Example of adding a trigger for date Timestamp.
=============================
CREATE OR REPLACE TRIGGER MY_TABLE_TG
  BEFORE UPDATE OR INSERT ON MY_TABLE
  FOR EACH ROW
BEGIN
   :NEW.upd_date := SYSDATE;
END;