CREATE TRIGGER

SET DELIMITER @
CREATE TRIGGER trg_hockey_insert FOR hockey.hockey BEFORE INSERT
AS
    IF (new.team <> 'Bruins')
        THROW 'Team Must Be "Bruins"';
    END_IF;
END_TRIGGER
@
SET DELIMITER ;

INSERT INTO hockey.hockey (number, name, position, team)
                   VALUES (99,'BOBBY ORR','Forward','Bruins');
INSERT INTO hockey.hockey (number, name, position, team)
                   VALUES (2, 'TOM JONES', 'Fan', 'Rangers');
Team Must Be "Bruins"

SELECT * FROM hockey.hockey WHERE number IN (99,2);

DROP TABLE IF EXISTS hockey_fan CASCADE;
CREATE TABLE hockey.hockey_fan (id          INTEGER GENERATED ALWAYS AS IDENTITY,
                         last_name   STRING,
                         first_name  STRING,
                         full_name   STRING,
                         teamid      CHAR(3),
                         create_date DATE DEFAULT DATE('now'),
                         modify_date DATE );
SET DELIMITER @
CREATE TRIGGER trg_hockeyfan_b_insert FOR hockey_fan BEFORE INSERT
AS
    new.full_name = new.first_name||' '||new.last_name;
END_TRIGGER
@

SET DELIMITER ;
INSERT INTO hockey_fan (last_name, first_name, teamid)
                VALUES ('JONES','TOM','BOS');

SELECT * FROM hockey_fan;

DROP TABLE IF EXISTS audit.hockey_fan_audit CASCADE;
CREATE TABLE audit.hockey_fan_audit (id          STRING NOT NULL,
                               old_teamid  CHAR(3),
                               new_teamid  CHAR(3),
                               audit_date  DATE DEFAULT DATE('now') );
SET DELIMITER @
CREATE TRIGGER trg_hockeyfan_b_update FOR hockey.hockey_fan BEFORE UPDATE
AS
    new.full_name = new.first_name || ' ' || new.last_name;
    new.modify_date = DATE('now');
END_TRIGGER
@

CREATE TRIGGER trg_hockeyfan_a_update FOR hockey.hockey_fan AFTER UPDATE
AS
    IF (old.teamid != new.teamid)
        INSERT INTO audit.hockey_fan_audit (id, old_teamid, new_teamid)
                            VALUES (old.id, old.teamid, new.teamid);
    END_IF;
END_TRIGGER
@
SET DELIMITER ;


SELECT * FROM hockey.hockey_fan;    /* Created in Example 3 */

UPDATE hockey.hockey_fan
   SET teamid = 'NYR'
  WHERE last_name = 'JONES' and first_name = 'TOM';

SELECT * FROM hockey.hockey_fan;

SELECT * FROM audit.hockey_fan_audit;

USE HOCKEY;
DROP TABLE IF EXISTS player_years;
CREATE TABLE player_years (Player_Id    STRING NOT NULL,
                           Years_Played  STRING);
SET DELIMITER @
CREATE TRIGGER trg_player_years_before_insert FOR Player_years BEFORE INSERT
  AS
    VAR l_Years_Played string = '';
    FOR SELECT DISTINCT year AS team_year
           FROM scoring
           WHERE playerid = new.Player_Id;
         l_Years_Played = l_Years_Played || team_year || ',';
    END_FOR;
    new.Years_Played = substr(l_Years_Played,1,length(l_Years_Played) -1);
END_TRIGGER;
@

SET DELIMITER ;

INSERT INTO Player_Years (Player_Id) SELECT playerId FROM players limit 5;

SELECT * FROM Player_Years;

CREATE TABLE ddl_operations (schema_name STRING, object_name STRING, operation_type STRING);
SET DELIMITER @
CREATE TRIGGER ddl_trigger_after_create_table ON DATABASE AFTER CREATE TABLE
AS
INSERT INTO ddl_operations VALUES (DDL_SCHEMA_NAME, DDL_OBJECT_NAME, DDL_OPERATION_TYPE);
END_TRIGGER;
@

SET DELIMITER ;
CREATE TABLE a.table1 (value INT);
CREATE TABLE b.table2 (value INT);

SELECT * FROM ddl_operations;

CREATE TABLE user_log (user_name STRING, operation_type STRING, timestamp TIMESTAMP WITHOUT TIME ZONE);
SET DELIMITER @
CREATE TRIGGER ddl_trigger_after_create_user ON DATABASE AFTER CREATE USER
AS
INSERT INTO user_log VALUES (DDL_OBJECT_NAME, DDL_OPERATION_TYPE, now());
END_TRIGGER;

CREATE TRIGGER ddl_trigger_after_drop_user ON DATABASE AFTER DROP USER
AS
INSERT INTO user_log VALUES (DDL_OBJECT_NAME, DDL_OPERATION_TYPE, now());
END_TRIGGER;
@

SET DELIMITER ;
CREATE USER alice PASSWORD 'blueCat123';
CREATE USER bob PASSWORD 'greenDog456';
DROP USER ALICE;

SELECT * FROM user_log;