17.00 - Example: Calling an SQL Procedure From Within a Trigger - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

The following trigger is used to track changes to documents. There is a document table and a journal table. As changes are made to the document table, the trigger changes the journal table. A separate application processes and removes rows from the journal table. The trigger ensures that there is at most one row in the journal table for each corresponding row in the document table. So, if a document is changed more than once, then the trigger maintains a single journal row for that document.

     CREATE TABLE document_table (
       docnum   INTEGER, 
       document BLOB)
     UNIQUE PRIMARY INDEX(docnum);
     CREATE TABLE document_journal_table (
       docnum INTEGER, 
       action CHARARACTER(1))
     UNIQUE PRIMARY INDEX(docnum);
     CREATE TRIGGER log_inserts_to_doc AFTER INSERT ON document_table
      REFERENCING new AS new_row
      FOR EACH ROW
      BEGIN ATOMIC
        CALL insert_doc_journal( new_row.docnum );
      END;
     CREATE PROCEDURE insert_doc_journal(IN docnum INTEGER)
       BEGIN
         DECLARE num_delete_journal_records INTEGER;
           SELECT COUNT(docnum) INTO :num_delete_journal_records
           FROM document_journal_table
           WHERE docnum=:docnum 
           AND   action = 'D';
        IF num_delete_journal_records > 0 THEN
          -- We have a delete journal record.  So, this document
          -- was previously deleted and now they are inserting it.
          -- A delete followed by an insert is really an update so
          -- remove the delete journal record and add an update
          -- change the journal record
           UPDATE document_journal_table 
           SET action = 'U' 
           WHERE docnum = :docnum;
        ELSE
          -- We either have no journal records or we have an insert or
          -- update record. An insert followed by an insert is still an
          -- insert. An insert followed by an update is really an insert
          -- so remove any journal record and add an insert journal           -- record.
           UPDATE document_journal_table 
           SET action = 'I' 
           WHERE docnum = :docnum;
           ELSE INSERT INTO document_journal_table VALUES (:docnum,'I');
        END IF;
     END;