MIS 385/MBA 664 Systems Implementation with DBMS/ Database ManagementProcedures, triggers & macros in TeradataTriggers in TeradataOne sample triggerGeneral syntax for triggersCode for delete_customer triggerShow date/time with SELECT & EXTRACTThe procedure from the bookEmbedded and Dynamic SQLMIS 385/MBA 664Systems Implementation with DBMS/Database ManagementDave [email protected] (email)http://www.davesalisbury.com/ (web site)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 commandTriggers 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 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_StateGeneral 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 triggerCREATE TRIGGER delete_customer AFTER DELETE ON CUSTOMER_TREFERENCING OLD as OFOR EACH ROWINSERT 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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 macrosThe procedure from the bookFirst alter the product_t tableThen create the procedureThen call the procedureEmbedded 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