Comprehensive Guide to Procedures, Triggers, and Macros in Teradata

1. MIS 385/MBA 664Systems Implementation with DBMS/Database Management Dave Salisbury salisbury@udayton.edu (email) http://www.davesalisbury.com/ (web site)

2. Procedures, triggers & macros in Teradata • Stored Procedures • Program modules that execute on demand • Written in SQL • Control and condition handling statements • More powerful than macros; more sophisticated procedural logic and error handling • Triggers • Routines that execute in response to a database event • e.g. INSERT, UPDATE, or DELETE • Macros • Series of SQL statements • Executable with a single command

3. Triggers in Teradata • Triggers are procedures associated with a table • Fire upon meeting of trigger condition • Used in database to react to specific situations • enforcing data integrity rules • deriving specific values • Two general types • Row triggers • Statement triggers • Simple example of using a trigger might be the autonumdatatype in Access (when calling a stored procedure to increment the value by 1)

4. One sample trigger • This sample will store the following fields in a table (old_customer_t) on the occurrence of the event (deletion of a customer (NOTE: need to create the table first). • Customer_ID • Customer_Name • Customer_City • Customer_State

5. General syntax for triggers • CREATE[REPLACE] <trigger_name> • ENABLED[DISABLED] BEFORE|AFTER • INSERT|DELETE OF <column_name> • ON <table_name> • REFERENCING OLD [row|table] AS <before processed row> • NEW [row|table] AS <after processed row> • FOR EACH ROW|STATEMENT • WHEN (search_condition SQL statement) • <triggered action to be performed>;

6. Code for delete_customer trigger CREATE TRIGGER delete_customer AFTER DELETE ON CUSTOMER_T REFERENCING OLD as O FOR EACH ROW INSERT INTO ARCHIVE_CUSTOMER_T (O.Customer_ID, O.Customer_Name, O.Customer_City, O.Customer_State);

7. Show date/time with SELECT & EXTRACT • To see current SYSTEM DATE • SELECT DATE; - gives output as “11/11/2008″ • EXTRACT • SELECT EXTRACT (MONTH FROM date); - “11” • SELECT EXTRACT (YEAR FROM date); - “2008” • SELECT EXTRACT (DAY FROM date); - “11” • For HOUR MINUTE and SECONDS extractions, use TIME instead. • SELECT TIME; - gives output as “18:25:01” • SELECT EXTRACT (MINUTE FROM time); - “18” • SELECT EXTRACT (HOUR FROM time); - “25” • SELECT EXTRACT (SECOND FROM time); - “01” • All of the above can be implemented using macros

8. The procedure from the book First alter the product_t table Then create the procedure Then call the procedure

9. Embedded and Dynamic SQL • Embedded SQL • Including hard-coded SQL statements in a program written in another language such as C or Java • Dynamic SQL • Ability for an application program to generate SQL code on the fly, as the application is running