SET SQL DIALECT 3;

/******************************************************************************/
/****                               Domains                                ****/
/******************************************************************************/

CREATE DOMAIN "dom_BOOL" AS
SMALLINT
NOT NULL
CHECK (VALUE IN (0, 1));

CREATE DOMAIN "dom_INT16" AS
SMALLINT;

CREATE DOMAIN "dom_INT64" AS
BIGINT;

CREATE DOMAIN "dom_PK" AS
BIGINT
NOT NULL;

CREATE DOMAIN "dom_SYS$NAME" AS
VARCHAR(33) CHARACTER SET UNICODE_FSS;

CREATE DOMAIN "dom_Statement" AS
VARCHAR(1000) CHARACTER SET ISO8859_1;

/******************************************************************************/
/****                              Generators                              ****/
/******************************************************************************/

CREATE GENERATOR "gen_PK";
CREATE GENERATOR "gen_TABLE1";
CREATE GENERATOR "gen_TABLE2";
CREATE GENERATOR "gen_TABLE3";
CREATE GENERATOR "gen_TABLE3ACTIVE";

/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/

CREATE GLOBAL TEMPORARY TABLE "_TABLE_ROW_COUNT" (
    "rcn_generator"  "dom_SYS$NAME" NOT NULL,
    "rcn_count"      "dom_INT16" NOT NULL
) ON COMMIT DELETE ROWS;

CREATE TABLE TABLE1 (
    "PK_tb1"    "dom_PK",
    "tb1_name"  "dom_SYS$NAME"
);

CREATE TABLE TABLE2 (
    "PK_tb2"    "dom_PK",
    "tb2_name"  "dom_SYS$NAME"
);

CREATE TABLE TABLE3 (
    "PK_tb3"      "dom_PK",
    "tb3_name"    "dom_SYS$NAME",
    "tb3_active"  "dom_BOOL"
);

/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY ("PK_tb1");
ALTER TABLE TABLE2 ADD CONSTRAINT PK_TABLE2 PRIMARY KEY ("PK_tb2");
ALTER TABLE TABLE3 ADD CONSTRAINT PK_TABLE3 PRIMARY KEY ("PK_tb3");

/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/

SET TERM ^ ;

/******************************************************************************/
/****                       Database event triggers                        ****/
/******************************************************************************/

/* Trigger: ROW_COUNT_COMMIT */
CREATE OR ALTER TRIGGER ROW_COUNT_COMMIT
ACTIVE ON TRANSACTION COMMIT POSITION 0
AS
DECLARE VARIABLE SQL TYPE OF "dom_Statement";
BEGIN
  /* alle Daten aus der Zähltabelle aufsummieren, */
  /* Statement bauen und ausführen                */
  FOR
      SELECT 'EXECUTE BLOCK AS '                                ||
             'DECLARE VARIABLE DUMMY TYPE OF "dom_INT64"; '     ||
             'BEGIN '                                           ||
               'DUMMY = GEN_ID('                                ||
                          "rcn_generator" || ', '               ||
                          CAST(SUM("rcn_count") AS VARCHAR(20)) ||
                       '); '                                    ||
             'END'
        FROM "_TABLE_ROW_COUNT"
    GROUP BY "rcn_generator"
      HAVING SUM("rcn_count") <> 0
  INTO :SQL
  DO
    EXECUTE STATEMENT SQL;
END
^

/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/

/* Trigger: TABLE1_AD0 */
CREATE OR ALTER TRIGGER TABLE1_AD0 FOR TABLE1
ACTIVE AFTER DELETE POSITION 0
AS
BEGIN
  /* Datensatzanzahländerung merken */
  INSERT INTO "_TABLE_ROW_COUNT"
    ("rcn_generator", "rcn_count")
  VALUES
    ('"gen_TABLE1"', -1); /* einen Datensatz gelöscht */
END
^

/* Trigger: TABLE1_AI0 */
CREATE OR ALTER TRIGGER TABLE1_AI0 FOR TABLE1
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
  /* Datensatzanzahländerung merken */
  INSERT INTO "_TABLE_ROW_COUNT"
    ("rcn_generator", "rcn_count")
  VALUES
    ('"gen_TABLE1"', 1); /* einen Datensatz hinzugefügt */
END
^

/* Trigger: TABLE1_BI0 */
CREATE OR ALTER TRIGGER TABLE1_BI0 FOR TABLE1
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  NEW."PK_tb1" = GEN_ID("gen_PK", 1);
END
^

/* Trigger: TABLE2_AID0 */
CREATE OR ALTER TRIGGER TABLE2_AID0 FOR TABLE2
ACTIVE AFTER INSERT OR DELETE POSITION 0
AS
DECLARE VARIABLE INC TYPE OF "dom_INT16";
BEGIN
  /* beim Einfügen */
  IF (INSERTING)
  THEN
    INC = 1;
  ELSE
  /* beim Löschen */
  IF (DELETING)
  THEN 
    INC = -1;
  ELSE
    /* keine Datensatzänderung */
    INC = 0;
  /* Datensatzanzahländerung merken */
  IF (INC <> 0)
  THEN 
    INSERT INTO "_TABLE_ROW_COUNT"
      ("rcn_generator", "rcn_count")
    VALUES
      ('"gen_TABLE2"', :INC);
END
^

/* Trigger: TABLE2_BI0 */
CREATE OR ALTER TRIGGER TABLE2_BI0 FOR TABLE2
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  NEW."PK_tb2" = GEN_ID("gen_PK", 1);
END
^

/* Trigger: TABLE3_AID0 */
CREATE OR ALTER TRIGGER TABLE3_AID0 FOR TABLE3
ACTIVE AFTER INSERT OR DELETE POSITION 0
AS
DECLARE VARIABLE INC TYPE OF "dom_INT16";
BEGIN
  /* beim Einfügen */
  IF (INSERTING)
  THEN
    INC = 1;
  ELSE
  /* beim Löschen */
  IF (DELETING)
  THEN 
    INC = -1;
  ELSE
    /* keine Datensatzänderung */
    INC = 0;
  /* Datensatzanzahländerung merken */
  IF (INC <> 0)
  THEN 
    INSERT INTO "_TABLE_ROW_COUNT"
      ("rcn_generator", "rcn_count")
    VALUES
      ('"gen_TABLE3"', :INC);
END
^

/* Trigger: TABLE3_AIUD1 */
CREATE OR ALTER TRIGGER TABLE3_AIUD1 FOR TABLE3
ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 1
AS
DECLARE VARIABLE INC TYPE OF "dom_INT16";
BEGIN
  /* Variable vorbelegen */
  INC = 0;
  /* beim Einfügen nur Flags <> 0 zählen */
  IF (INSERTING)
  THEN
  BEGIN
    IF (NEW."tb3_active" <> 0)
    THEN
      INC = 1;
  END
  ELSE
  /* beim Löschen nur Flags <> 0 zählen */
  IF (DELETING)
  THEN 
  BEGIN
    IF (OLD."tb3_active" <> 0)
    THEN
      INC = -1;
  END
  ELSE
  /* beim Aktualisieren nur geänderte Flags zählen */
  IF (UPDATING)
  THEN 
  BEGIN
    /* Flag wurde gelöscht? */
    IF (OLD."tb3_active" <> 0 AND NEW."tb3_active" = 0)
    THEN
      INC = -1;
    ELSE
      /* Flag wurde gesetzt? */
      IF (OLD."tb3_active" = 0 AND NEW."tb3_active" <> 0)
      THEN
        INC = 1;
  END
  /* Zähler <> 0 eintragen */
  IF (INC <> 0)
  THEN 
    INSERT INTO "_TABLE_ROW_COUNT"
      ("rcn_generator", "rcn_count")
    VALUES
      ('"gen_TABLE3ACTIVE"', :INC);
END
^

/* Trigger: TABLE3_BI0 */
CREATE OR ALTER TRIGGER TABLE3_BI0 FOR TABLE3
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  NEW."PK_tb3" = GEN_ID("gen_PK", 1);
END
^

SET TERM ; ^

/******************************************************************************/
/****                             Descriptions                             ****/
/******************************************************************************/

COMMENT ON DOMAIN "dom_BOOL" IS 
'boolscher Wert';

COMMENT ON DOMAIN "dom_INT16" IS 
'16 bit integer';

COMMENT ON DOMAIN "dom_INT64" IS 
'64 bit integer';

COMMENT ON DOMAIN "dom_PK" IS 
'Primärschlüssel';

COMMENT ON DOMAIN "dom_SYS$NAME" IS 
'Systemnamen';

COMMENT ON DOMAIN "dom_Statement" IS 
'Zeichenkette für SQL-Statements';

/******************************************************************************/
/****                             Descriptions                             ****/
/******************************************************************************/

COMMENT ON TABLE TABLE1 IS 
'Datensatzzähler mit zwei Triggern: AFTER INSERT und AFTER DELETE';

COMMENT ON TABLE TABLE2 IS 
'Datensatzzähler mit einem Trigger: AFTER INSERT OR DELETE';

COMMENT ON TABLE TABLE3 IS 
'Datensatz- und Aktivzähler mit je einem Trigger: AFTER INSERT OR DELETE bzw. AFTER INSERT OR UPDATE OR DELETE';

COMMENT ON TABLE "_TABLE_ROW_COUNT" IS 
'temporäre Tabelle für Datensatzzählungen';

/******************************************************************************/
/****                             Descriptions                             ****/
/******************************************************************************/

COMMENT ON TRIGGER ROW_COUNT_COMMIT IS 
'temporäre Datensatzzählungen bei COMMIT in die Generatoren übertragen';

COMMENT ON TRIGGER TABLE1_AD0 IS 
'Datensatzzähler Tabelle 1: Datensatz löschen';

COMMENT ON TRIGGER TABLE1_AI0 IS 
'Datensatzzähler Tabelle 1: Datensatz einfügen';

COMMENT ON TRIGGER TABLE1_BI0 IS 
'Tabelle 1: Primärschlüssel';

COMMENT ON TRIGGER TABLE2_AID0 IS 
'Datensatzzähler Tabelle 2: Datensatz einfügen oder löschen';

COMMENT ON TRIGGER TABLE2_BI0 IS 
'Tabelle 2: Primärschlüssel';

COMMENT ON TRIGGER TABLE3_AID0 IS 
'Datensatzzähler Tabelle 3: Datensatz einfügen oder löschen';

COMMENT ON TRIGGER TABLE3_AIUD1 IS
'Aktivzähler Tabelle 3: Datensatz einfügen, löschen oder Aktiv-Flag ändern';

COMMENT ON TRIGGER TABLE3_BI0 IS 
'Tabelle 2: Primärschlüssel';

/******************************************************************************/
/****                             Descriptions                             ****/
/******************************************************************************/

COMMENT ON GENERATOR "gen_PK" IS 
'Primärschlüssel';

COMMENT ON GENERATOR "gen_TABLE1" IS 
'Datensatzzähler Tabelle 1';

COMMENT ON GENERATOR "gen_TABLE2" IS 
'Datensatzzähler Tabelle 2';

COMMENT ON GENERATOR "gen_TABLE3" IS 
'Datensatzzähler Tabelle 3';

COMMENT ON GENERATOR "gen_TABLE3ACTIVE" IS 
'Aktivzähler Tabelle 3';

/******************************************************************************/
/****                         Fields descriptions                          ****/
/******************************************************************************/

COMMENT ON COLUMN TABLE1."PK_tb1" IS 
'Primärschlüssel';

COMMENT ON COLUMN TABLE1."tb1_name" IS 
'Name';

COMMENT ON COLUMN TABLE2."PK_tb2" IS 
'Primärschlüssel';

COMMENT ON COLUMN TABLE2."tb2_name" IS 
'Name';

COMMENT ON COLUMN TABLE3."PK_tb3" IS 
'Primärschlüssel';

COMMENT ON COLUMN TABLE3."tb3_name" IS 
'Name';

COMMENT ON COLUMN TABLE3."tb3_active" IS 
'Datensatz aktiv';

COMMENT ON COLUMN "_TABLE_ROW_COUNT"."rcn_generator" IS 
'Systemnamen';

COMMENT ON COLUMN "_TABLE_ROW_COUNT"."rcn_count" IS 
'16 bit integer';

/******************************************************************************/
/****                              Privileges                              ****/
/******************************************************************************/

/* Privileges of users */
GRANT ALL ON RDB$FORMATS TO DBUSER WITH GRANT OPTION;
GRANT ALL ON RDB$PAGES TO DBUSER WITH GRANT OPTION;
GRANT ALL ON RDB$ROLES TO DBUSER WITH GRANT OPTION;
GRANT ALL ON TABLE1 TO DBUSER WITH GRANT OPTION;
GRANT ALL ON TABLE2 TO DBUSER WITH GRANT OPTION;
GRANT ALL ON TABLE3 TO DBUSER WITH GRANT OPTION;
GRANT ALL ON "_TABLE_ROW_COUNT" TO DBUSER WITH GRANT OPTION;
GRANT SELECT ON RDB$FORMATS TO PUBLIC;
GRANT SELECT ON RDB$PAGES TO PUBLIC;
GRANT SELECT ON RDB$ROLES TO PUBLIC;

/* Privileges of triggers */
GRANT ALL ON "_TABLE_ROW_COUNT" TO TRIGGER ROW_COUNT_COMMIT;
GRANT UPDATE, REFERENCES ON TABLE1 TO TRIGGER TABLE1_AD0;
GRANT ALL ON "_TABLE_ROW_COUNT" TO TRIGGER TABLE1_AD0;
GRANT UPDATE, REFERENCES ON TABLE1 TO TRIGGER TABLE1_AI0;
GRANT ALL ON "_TABLE_ROW_COUNT" TO TRIGGER TABLE1_AI0;
GRANT UPDATE, REFERENCES ON TABLE1 TO TRIGGER TABLE1_BI0;
GRANT UPDATE, REFERENCES ON TABLE2 TO TRIGGER TABLE2_AID0;
GRANT ALL ON "_TABLE_ROW_COUNT" TO TRIGGER TABLE2_AID0;
GRANT UPDATE, REFERENCES ON TABLE2 TO TRIGGER TABLE2_BI0;
GRANT UPDATE, REFERENCES ON TABLE3 TO TRIGGER TABLE3_AID0;
GRANT ALL ON "_TABLE_ROW_COUNT" TO TRIGGER TABLE3_AID0;
GRANT UPDATE, REFERENCES ON TABLE3 TO TRIGGER TABLE3_AIUD1;
GRANT ALL ON "_TABLE_ROW_COUNT" TO TRIGGER TABLE3_AIUD1;
GRANT UPDATE, REFERENCES ON TABLE3 TO TRIGGER TABLE3_BI0;