/********************* ROLES **********************/

/********************* UDFS ***********************/

/****************** SEQUENCES ********************/

/******************** DOMAINS *********************/

CREATE DOMAIN MON$SEC_DATABASE
 AS char(7) CHARACTER SET ASCII
 NOT NULL
 COLLATE ASCII;
CREATE DOMAIN SEC$KEY
 AS varchar(10) CHARACTER SET UNICODE_FSS
 COLLATE UNICODE_FSS;
CREATE DOMAIN SEC$NAME_PART
 AS varchar(10) CHARACTER SET UNICODE_FSS
 COLLATE UNICODE_FSS;
CREATE DOMAIN SEC$USER_NAME
 AS varchar(10) CHARACTER SET UNICODE_FSS
 COLLATE UNICODE_FSS;
CREATE DOMAIN SEC$VALUE
 AS varchar(85) CHARACTER SET UNICODE_FSS
 COLLATE UNICODE_FSS;
/******************* PROCEDURES ******************/

/******************** TABLES **********************/

CREATE TABLE ATRIBUTO
(
  TABLA char(50) NOT NULL,
  ULTIMO integer NOT NULL,
  CONSTRAINT PK_ATRIBUTO PRIMARY KEY (TABLA)
);
CREATE TABLE CLIENTE
(
  ID integer NOT NULL,
  NOMBRE varchar(100),
  CONSTRAINT PK_CLIENTE PRIMARY KEY (ID)
);
/********************* VIEWS **********************/

/******************* EXCEPTIONS *******************/

CREATE EXCEPTION EX_ERRORID
'No se puede cambiar ID!!';
CREATE EXCEPTION EX_ERRULTIMO
'No puede borrar intermedios!';
/******************** TRIGGERS ********************/

SET TERM ^ ;
CREATE TRIGGER TRG_ADCLIENTE FOR CLIENTE ACTIVE
AFTER delete POSITION 0
AS
BEGIN
    /* enter trigger code here */
    UPDATE ATRIBUTO SET ULTIMO = ULTIMO - 1 WHERE TABLA = 'CLIENTE'; 
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_AICLIENTE FOR CLIENTE ACTIVE
AFTER insert POSITION 0
AS 
   DECLARE VARIABLE ULTIMO TYPE OF COLUMN ATRIBUTO.ULTIMO;
BEGIN 
    /* enter trigger code here */
    SELECT ULTIMO FROM ATRIBUTO WHERE TABLA = 'CLIENTE' INTO :ULTIMO;
    IF ( ULTIMO IS NULL ) THEN INSERT INTO ATRIBUTO(TABLA,ULTIMO) VALUES('CLIENTE',1);
    ELSE UPDATE ATRIBUTO SET ULTIMO = NEW.ID WHERE TABLA = 'CLIENTE';
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BDCLIENTE FOR CLIENTE ACTIVE
BEFORE delete POSITION 0
AS
BEGIN
    /* enter trigger code here */ 
    IF ( OLD.ID <> (SELECT ULTIMO FROM ATRIBUTO WHERE TABLA = 'CLIENTE') ) THEN EXCEPTION EX_ERRULTIMO;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BICLIENTE FOR CLIENTE ACTIVE
BEFORE insert POSITION 0
AS 
   DECLARE VARIABLE ULTIMO TYPE OF COLUMN ATRIBUTO.ULTIMO;
BEGIN 
    /* enter trigger code here */
    SELECT ULTIMO FROM ATRIBUTO WHERE TABLA = 'CLIENTE' INTO :ULTIMO;
    IF ( ULTIMO IS NULL ) THEN NEW.ID = 1;
    ELSE NEW.ID = ULTIMO + 1;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BUCLIENTE FOR CLIENTE ACTIVE
BEFORE update POSITION 0
AS
BEGIN
    /* enter trigger code here */ 
    IF ( NEW.ID <> OLD.ID ) THEN EXCEPTION EX_ERRORID;
END^
SET TERM ; ^

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON ATRIBUTO TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON CLIENTE TO  SYSDBA WITH GRANT OPTION;

