Transcript MIS 301- Database

MIS 385/MBA 664
Systems Implementation with DBMS/
Database Management
Dave Salisbury
[email protected] (email)
http://www.davesalisbury.com/ (web site)
Procedures, triggers & macros in Teradata

Stored Procedures





Triggers



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
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
Triggers in Teradata



Triggers are procedures associated with a table
Fire upon meeting of trigger condition
Used in database to react to specific situations



Two general types



enforcing data integrity rules
deriving specific values
Row triggers
Statement triggers
Simple example of using a trigger might be the
autonum datatype in Access (when calling a stored
procedure to increment the value by 1)
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
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>;
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);
Show date/time with SELECT & EXTRACT



To see current SYSTEM DATE
SELECT DATE; - gives output as “11/11/2008″
EXTRACT





For HOUR MINUTE and SECONDS extractions, use
TIME instead.
SELECT TIME; - gives output as “18:25:01”




SELECT EXTRACT (MONTH FROM date); - “11”
SELECT EXTRACT (YEAR FROM date); - “2008”
SELECT EXTRACT (DAY FROM date); - “11”
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
The procedure from the book
First alter the product_t table
Then create the procedure
Then call the procedure
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