Avertissements

CREATE TRIGGER tr_log_connect
INACTIVE ON CONNECT POSITION 0
AS
BEGIN
  INSERT INTO LOG_CONNECT (ID,
                           USERNAME,
                           ATIME)
  VALUES (NEXT VALUE FOR SEQ_LOG_CONNECT,
          CURRENT_USER,
          CURRENT_TIMESTAMP);
END

CREATE EXCEPTION E_INCORRECT_WORKTIME 'La journée de travail n''a pas encore commencé';

CREATE TRIGGER TR_LIMIT_WORKTIME ACTIVE
ON CONNECT POSITION 1
AS
BEGIN
  IF ((CURRENT_USER <> 'SYSDBA') AND
      NOT (CURRENT_TIME BETWEEN time '9:00' AND time '17:00')) THEN
     EXCEPTION E_INCORRECT_WORKTIME;
END

CREATE EXCEPTION e_invalid_sp_name 'Mauvais nom de la procédure stockée (devrait commencer par SP_)';

SET TERM !;

CREATE TRIGGER trig_ddl_sp BEFORE CREATE PROCEDURE
AS
BEGIN
  IF (rdb$get_context('DDL_TRIGGER', 'OBJECT_NAME')
      NOT STARTING 'SP_') THEN
    EXCEPTION e_invalid_sp_name;
END!

-- Test
CREATE PROCEDURE sp_test
AS
BEGIN
END!

CREATE PROCEDURE test
AS
BEGIN
END!

-- Statement failed, SQLSTATE = 42000
-- exception 1
-- -E_INVALID_SP_NAME
-- -Mauvais nom de la procédure stockée (devrait commencer par SP_)
-- -At trigger 'TRIG_DDL_SP' line: 4, col: 5

SET TERM ;!

CREATE EXCEPTION e_access_denied 'Access denied';

SET TERM !;

CREATE TRIGGER trig_ddl BEFORE ANY DDL STATEMENT
AS
BEGIN
  IF (current_user <> 'SUPER_USER') THEN
    EXCEPTION e_access_denied;
END!

-- Test
CREATE PROCEDURE sp_test
AS
BEGIN
END!

-- The last command raises this exception and procedure SP_TEST is not created
-- Statement failed, SQLSTATE = 42000
-- exception 1
-- -E_ACCESS_DENIED
-- -Access denied
-- -At trigger 'TRIG_DDL' line: 4, col: 5

SET TERM ;!

CREATE SEQUENCE ddl_seq;

CREATE TABLE ddl_log (
  id BIGINT NOT NULL PRIMARY KEY,
  moment TIMESTAMP NOT NULL,
  user_name VARCHAR(63) NOT NULL,
  event_type VARCHAR(25) NOT NULL,
  object_type VARCHAR(25) NOT NULL,
  ddl_event VARCHAR(25) NOT NULL,
  object_name VARCHAR(63) NOT NULL,
  old_object_name VARCHAR(63),
  new_object_name VARCHAR(63),
  sql_text BLOB sub_type text NOT NULL,
  ok CHAR(1) NOT NULL
);

SET TERM !;

CREATE TRIGGER trig_ddl_log_before BEFORE ANY DDL STATEMENT
AS
  DECLARE id TYPE OF COLUMN ddl_log.id;
BEGIN
  -- Nous devons apporter des changements à la TRANSACTION AUTONOME,
  -- de cette façon, si une exception se produit et que la commande
  -- n'est pas exécutée, elle sera quand même enregistrée.
  IN AUTONOMOUS TRANSACTION DO
  BEGIN
    INSERT INTO ddl_log (
      id, moment, user_name, event_type, object_type, ddl_event,
      object_name, old_object_name, new_object_name, sql_text, ok)
    VALUES (NEXT VALUE FOR ddl_seq,
            current_timestamp, current_user,
            rdb$get_context('DDL_TRIGGER', 'EVENT_TYPE'),
            rdb$get_context('DDL_TRIGGER', 'OBJECT_TYPE'),
            rdb$get_context('DDL_TRIGGER', 'DDL_EVENT'),
            rdb$get_context('DDL_TRIGGER', 'OBJECT_NAME'),
            rdb$get_context('DDL_TRIGGER', 'OLD_OBJECT_NAME'),
            rdb$get_context('DDL_TRIGGER', 'NEW_OBJECT_NAME'),
            rdb$get_context('DDL_TRIGGER', 'SQL_TEXT'),
            'N')
    RETURNING id INTO id;
    rdb$set_context('USER_SESSION', 'trig_ddl_log_id', id);
  END
END!

-- Note :
-- Le déclencheur créé ci-dessus sera exécuté pour cette DDL.
-- Une bonne idée est d'utiliser -nodbtriggers
-- quand on travaille avec lui
CREATE TRIGGER trig_ddl_log_after AFTER ANY DDL STATEMENT
AS
BEGIN
  -- Dans ce cas, nous avons besoin d'une transaction autonome,
  -- parce que dans la transaction originale
  -- nous ne verrons pas l'enregistrement inséré dans la base de données
  -- AVANT le déclenchement d'une transaction hors ligne,
  -- si la transaction de l'utilisateur n'est pas lancée
  -- avec le mode d'isolation READ COMMITTED.
  IN AUTONOMOUS TRANSACTION DO
    UPDATE ddl_log SET ok = 'Y'
    WHERE
      id = rdb$get_context('USER_SESSION', 'trig_ddl_log_id');
END!

COMMIT!

SET TERM ;!

-- Nous supprimons l'enregistrement de la création trig_ddl_log_after.

DELETE FROM ddl_log;
COMMIT;

-- Test

-- Cette commande sera enregistrée une fois
-- (puisque T1 n'existe pas, RECREATE déclenchera l'événement CREATE)
-- avec OK = Y.

RECREATE TABLE t1 (
  n1 INTEGER,
  n2 INTEGER
);

-- L'opérateur ne s'exécutera pas car T1 existe déjà,
-- donc OK aura une valeur de N.

CREATE TABLE t1 (
  n1 INTEGER,
  n2 INTEGER
);

-- T2 n'existe pas. Cette action ne sera pas enregistrée.

DROP TABLE t2;

-- Cette action sera enregistrée deux fois
-- (puisque T1 existe, l'action RECREATE est traitée
-- comme DROP et CREATE) avec le champ OK = Y.

RECREATE TABLE t1 (
  n INTEGER
);

CREATE DOMAIN dom1 AS INTEGER;

ALTER DOMAIN dom1 TYPE BIGINT;

ALTER DOMAIN dom1 TO dom2;

COMMIT;

SELECT
  id,
  ddl_event,
  object_name as name,
  sql_text,
  ok
FROM ddl_log
ORDER BY id;

 ID DDL_EVENT                 OBJECT_NAME                      SQL_TEXT OK
=== ========================= ======================= ================= ======
  2 CREATE TABLE              T1                                   80:3 Y
====================================================
SQL_TEXT:
recreate table t1 (
    n1 integer,
    n2 integer
)
====================================================
  3 CREATE TABLE              T1                                   80:2 N
====================================================
SQL_TEXT:
create table t1 (
    n1 integer,
    n2 integer
)
====================================================
  4 DROP TABLE                T1                                   80:6 Y
====================================================
SQL_TEXT:
recreate table t1 (
    n integer
)
====================================================
  5 CREATE TABLE              T1                                   80:9 Y
====================================================
SQL_TEXT:
recreate table t1 (
    n integer
)
====================================================

ALTER TRIGGER set_cust_no INACTIVE;

ALTER TRIGGER set_cust_no POSITION 14;

ALTER TRIGGER TR_CUST_LOG
INACTIVE AFTER INSERT OR UPDATE;

ALTER TRIGGER TR_CUST_LOG
SQL SECURITY DEFINER;

ALTER TRIGGER TR_CUST_LOG
DROP SQL SECURITY;

ALTER TRIGGER tr_log_connect
ACTIVE POSITION 1
AS
BEGIN
  INSERT INTO LOG_CONNECT (ID,
                           USERNAME,
                           ROLENAME,
                           ATIME)
  VALUES (NEXT VALUE FOR SEQ_LOG_CONNECT,
          CURRENT_USER,
          CURRENT_ROLE,
          CURRENT_TIMESTAMP);
END

CREATE OR ALTER TRIGGER set_cust_no
ACTIVE BEFORE INSERT ON customer POSITION 0
AS
BEGIN
  IF (NEW.cust_no IS NULL) THEN
    NEW.cust_no = GEN_ID(cust_no_gen, 1);
END

DROP TRIGGER set_cust_no;

RECREATE TRIGGER set_cust_no
ACTIVE BEFORE INSERT ON customer POSITION 0
AS
BEGIN
  IF (NEW.cust_no IS NULL) THEN
    NEW.cust_no = GEN_ID(cust_no_gen, 1);
END