19.3 TRIGGER

19.3.1 Overview

  • A trigger is a database object, which is a stored program to be executed by the DBMS whenever a specified event (insert, update, delete rows) occurs on its associated table.

  • Three trigger types: BEFORE, INSTEAD OF, and AFTER

  • Each type can be declared for Insert, Update, and Delete, resulting in a total of nine trigger types.

  • Oracle supports all nine trigger types.

  • SQL Server supports six trigger types (INSTEAD OF and AFTER).

  • MySQL supports six trigger types (BEFORE and AFTER).

19.3.2 Use

  1. A trigger is attached to a table or view.

  2. When a trigger is fired, the DBMS supplies:

  • Old and new values for the update
  • New values for inserts
  • Old values for deletions
  1. The way the values are supplied depends on the DBMS product.
  • (Oracle and MySQL) The old values are supplied by prefixing a column name with the expression old: and the new values are with new.
  1. Trigger applications include:
  • Providing default values
  • Enforce data constraints
  • Updating views
  • Performing referential integrity actions

19.3.3 Syntax

-- MySQL trigger syntax
CREATE TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

19.3.4 Prefixing

(Oracle and MySQL)

When an event fires a trigger, DBMS will provide the required column values.

  • The new values are accessed by the name NEW. The old values are accessed by the name OLD.

  • To access a particular column in the new values, use dot notation: NEW.column_name. For instance, NEW.FirstName.

  • A variable begins with the @ sign.

19.3.5 Provide Default Values

Before creating a new trigger, let’s take a look of the ARTIST table in the vrg database. There is no gender data. We will add a new column Gender to the table by running the following statement:

--run this block to delete the column if needed
ALTER TABLE artist 
  DROP COLUMN IF EXISTS Gender
;
--add a new column
ALTER TABLE artist 
    ADD COLUMN Gender CHAR(6) NULL AFTER firstname
;

After the Gender column has been added, we can write a BEFORE trigger set_artist_gender for INSERT events, which sets up a default gender for every new artist. Test the trigger with a new INSERT INTO statement.

DROP TRIGGER IF EXISTS set_artist_gender;

CREATE TRIGGER set_artist_gender
  BEFORE INSERT ON artist
    FOR EACH ROW SET NEW.Gender='male';
DELETE FROM artist WHERE artistid=20;

INSERT INTO artist 
  (ArtistID, lastname, firstname) 
VALUES
  (20,'yourlastname','yourfirstname')
;

SELECT * FROM artist WHERE artistid=20;

19.3.6 TODO-Provide Default with simple expressions

In the trans table, there are two columns: AcquisitionPrice and AskingPrice. AcquisitionPrice is required with NOT NULL constraint. AskingPrice is optional with NULL constraint.

We can write a BEFORE trigger for INSERT, set_trans_askingPrice, which sets AskingPrice equal to twice the AcquisitionPrice or to the AcquisitioPrice plus the average net gain in the past.

The expression is NEW.AcquisitionPrice*2.

To-do:

Revise the following script for the set_trans_askingPrice trigger as described previously.

DROP TRIGGER IF EXISTS set_trans_askingPrice;

CREATE TRIGGER set_trans_askingPrice
  BEFORE INSERT ON artist
    FOR EACH ROW SET NEW.Gender='male';
DELETE FROM artist WHERE artistid=20;

INSERT INTO artist 
  (ArtistID, lastname, firstname) 
VALUES
  (20,'yourlastname','yourfirstname')
;

SELECT * FROM artist WHERE artistid=20;