Triggers and Stored Procedure Language (SPL)

1. Triggers and Stored Procedure Language (SPL)

2. Agenda • Definition of a trigger. • Components of a trigger. • Trigger syntax. • Creation of a trigger. • Special trigger routines. • Triggers on views.. • Introduction to Stored Procedure Language (SPL).

3. Trigger • A trigger is a database mechanism that automatically performs one or more database operations when a certain database event occurs. • The database event can be an INSERT, UPDATE, DELETE or SELECT operation on a table or a view. • The operations that a trigger performs can be INSERT, UPDATE, DELETE, EXECUTE PROCEDURE or EXECUTE FUNCTION operations. • There can be multiple INSERT, UPDATE, SELECT or DELETE triggers created on the same table or view.

4. Trigger -Components • Trigger name • Identifies the trigger. • Must be unique within the database. • Trigger event • Database statement that activates the trigger. • The event can be INSERT, UPDATE, DELETE or SELECT statement. • For UPDATE or SELECT events, a single or multiple columns can be chosen on the table to activate the trigger. If no columns are chosen, an update or select on any column activates the trigger.

5. Trigger -Components (cont’d) • Trigger event(cont’d) • For INSERT or DELETE events, any insert or delete on the table activates the trigger. • A trigger is created on a local table or a view. It cannot be created on a table or a view which is on a remote database. • Trigger action • SQL statements that are executed when the trigger event occurs. • There are 3 types of trigger actions: BEFORE, AFTER and FOR EACH ROW.

6. Trigger -Components (cont’d) • Trigger action(cont’d) • BEFORE trigger action is executed before the triggering event occurs. • AFTER trigger action is executed after the triggering event occurs. • FOR EACH ROW trigger action is executed for each row that is affected by the triggering event. • The trigger actions can be an INSERT, UPDATE, DELETE, EXECUTE PROCEDURE or EXECUTE FUNCTION statements.

7. Trigger: Creation syntax CREATE TRIGGER update_quantity UPDATE OF quantity ON items BEFORE(EXECUTE PROCEDURE upd_items_p1) The above example creates an UPDATE trigger with a name update_quantity on the items table. The trigger will be activated when the column quantity gets updated. When this trigger gets activated, the stored procedure upd_items_p1 will be executed before the column quantity is updated.

8. Trigger -Creation(cont’d) • REFERENCING clause • Can only be used with FOR EACH ROW trigger actions. • Allows the old and/or new values of the column to be accessed. -- Syntax of the REFERENCING clause -- Defines two prefixes ‘pre_upd’ for the old value and -- ‘post_upd’ for the new value. REFERENCING OLD AS pre_upd NEW AS post_upd

9. Trigger -Creation (cont’d) • REFERENCING clause(cont’d) -- Create a table , create a trigger with the REFERENCING clause -- to access the old and new values of the quantity column and insert -- the values into the table. CREATE TABLE log_record (old_qty SMALLINT, new_qty SMALLINT); CREATE TRIGGER update_quantity UPDATE OF quantity ON items REFERENCING OLD AS pre_upd NEW AS post_upd FOR EACH ROW (INSERT INTO log_record VALUES (pre_upd.quantity, post_upd.quantity));

10. Trigger -Creation(cont’d) • Trigger routine • Special stored procedure for trigger usage only. • Can only be used with FOR EACH ROW trigger actions. • Allows the old and/or new values of the column to be accessed within the procedure body. • Has its own REFERENCING clause.

11. Trigger -Creation(cont’d) • Trigger routine(cont’d) -- Create trigger routine CREATE PROCEDURE trig_proc() REFERENCING OLD as pre NEW as post for items; if (INSERTING) then insert into log_records values(post.quantity,post.quantity); end if if (UPDATING) then insert into log_records values(pre.quantity,post.quantity); end if if (SELECTING) then insert into log_records values (pre.quantity,pre.quantity); end if if (DELETING) then delete from log_records; end if END PROCEDURE;

12. Trigger - Creation(cont’d) • Trigger routine (cont’d) • Uses Boolean operators that identifies the trigger type. These operators are INSERTING, UPDATING, DELETING and SELECTING. • Can only be executed as a trigger action using “WITH TRIGGER REFERENCES” clause. CREATE TRIGGER update_quantity UPDATE OF quantity ON items FOR EACH ROW ( EXECUTE PROCEDURE trig_prog() WITH TRIGGER REFERENCES);

13. Trigger - Creation(cont’d) • INSTEAD OF Trigger • Created on a view instead of a table. • Ignores the trigger event and executes the trigger action. • To insert, update, or delete rows in the base tables of a view, you can define an INSTEAD OF trigger.

14. Trigger -Creation(cont’d) • INSTEAD OF trigger (cont’d) --Creating INSTEAD OF trigger CREATE TRIGGER manager_info_update INSTEAD OF UPDATE ON manager_info_view REFERENCING NEW AS n FOR EACH ROW (EXECUTE PROCEDURE updtab (n.empno, n.empname, n.deptno,));

15. Stored Procedure Language (SPL) • Informix SPL Routines • Why use SPL Routines? • Create Procedure/Function • SPL Routine Overloading • SPL Overview • Exception Handling • Debug SPL Routines • Execute SPL Routines • Drop SPL Routines • Statistics • Summary • Resources

16. Informix SPL Routines • User-defined functions. • Written in its own language: • SPL (Stored Procedure Language) • an extension to SQL that provides flow control (looping and branching). • Includes SPL procedure and SPL function: • SPL procedure – doesn’t return a value. • SPL function – returns one or more values. • Parsed and optimized at creation time. • Stored in system catalog table in executable format.

17. Why use SPL Routines? • Improve performance: • Runs in database engine, less I/O. • Good for frequently repeated tasks (bypass parsing, validity checking, query optimization). • Reduces application complexity. • Flexible: • Callable in other SQL statements. • Useful in triggers. • Adds flow control to SQL. • Easy to maintain. • Limit and Monitor access to database data.

18. Create SPL Routine • SPL routine consists of a beginning statement, a statement block, and an ending statement. • Create Command. CREATE PROCEDURE name (parameter list) SPECIFIC name1 … {statement block} END PROCEDURE; CREATE FUNCTION name (parameter list) RETURNING list SPECIFIC name1 … {statement block} END FUNCTION;

19. Create SPL Routine (cont’d) • Create SPL routine from file. • File can contain only one: CREATE PROCEDURE/FUNCTION statement CREATE PROCEDURE FROM <file_name>; CREATE FUNCTION FROM <file_name>;

20. Create SPL Routine - Example CREATE PROCEDURE set_status (id INTEGER DEFAULT 0, val CHAR(25)) UPDATE invent SET invent.status = val WHERE invent.id = id; END PROCEDURE; CREATE FUNCTION val_comp (val1 INTEGER, val2 INTEGER) RETURNING INTEGER; DEFINE res INTEGER; IF (val1 = val2) THEN res = 0; ELSE res = 1; RETURN res; END FUNCTION;

21. SPL Routine Parameter • Pass as many as needed. • Limit 32K for total size of parameters. • Can be any SQL data type, except: • Serial/Serial8 • Text/Byte (can pass by REFERENCE) • Can be complex and user-defined data types. • Can specify DEFAULT value for a parameter.

22. SPL Function Returning Values • Use RETURNING or RETURNS keywords with a list of data types. • Can be any SQL data type, except: • Serial/Serial8 • Text/Byte (can pass by REFERENCE) • Must specify a RETURN statement in procedure body. • Display labels for the return values (none or all). CREATE FUNCTION val_comp (val1 INTEGER, val2 INTEGER) RETURNING INTEGER AS comp_res;

23. Create SPL Routine (cont’d) CREATE FUNCTION val_equal (val1 INTEGER, val2 INTEGER) RETURNING INTEGER WITH NO VARIANT ….. END PROCEDURE; DOCUMENT ‘This function checks whether the given two values are equal’ WITH LISTING IN ‘pathname’; • WITH NO VARIANT • Indicates that the routine cannot contain any SQL statement. • Functional index can be created on non-variant routine. • DOCUMENT • Specifies routine comments that can be selected from system catalog table. • WITH LISTING IN • Directs any compile-time warnings to the given file.

24. SPL Routine Overloading • Defines more than one routines with the same name. • SPL routine is uniquely identified by the routine signature: • Type of the routine (procedure or function). • Name of the routine. • Cardinality, data type, and order of the parameters. • Owner name (ANSI database only). CREATE FUNCTION val_comp (val1 INTEGER, val2 INTEGER) CREATE FUNCTION val_comp (val1 CHAR(25), val2 CHAR(25)) CREATE FUNCTION val_comp(val1 DECIMAL, val2 DECIMAL) …

25. SPL Routine Overloading (cont’d) • SPECIFIC routine name • A shorthand identifier that refers to a particular overloaded version of a SPL routine. • Specific name is unique in the database. • Specific name can be used in SQL statements: ALTER, DROP, GRANT, REVOKE, UPDATE STATISTICS CREATE FUNCTION val_comp (val1 INTEGER, val2 INTEGER) SPECIFIC int_comp CREATE FUNCTION val_comp (val1 CHAR(25), val2 CHAR(25)) SPECIFIC string_comp CREATE FUNCTION val_comp val1 DECIMAL, val2 DECIMAL) SPECIFIC dec_comp

26. SPL Routine – Statement Block • A group of SPL and SQL statements. • Between CREATE PROCEDURE/FUNCTION and END PROCEDURE/FUNCTION is an implicit statement block. • Use BEGIN and END to specify an explicit statement block nested within another statement block.

27. SPL Routine – Variables • Variables are defined in routine body with DEFINE statement. • Variables are held in memory, not in database. • Variables can be local or global. • Variables can be any SQL data type and extended data types, except: • Serial/Serial8 • Text/Byte (can be declared with REFERENCE keyword). DEFINE x, y INT; -- built-in data type DEFINE pt person_t; -- user defined data type DEFINE loc LIKE image.location; -- table column data type

28. SPL Routine – Variables (cont’d) • Local Variable: • Is valid only for the duration of the SPL routine. • Is reset each time the SPL routine is executed. • Cannot have default value. • Scope: • Local variable is available in the statement block in which it is defined, and within any nested statement block. • Local variables can be redefined in a statement block.

29. CREATE PROCEDURE scope() DEFINE x,y,z INT; LET x = 5; LET y = 10; LET z = x + y; --z is 15 BEGIN DEFINE x, q INT; -- x is redefined DEFINE z CHAR(5); -- z is redefine LET x = 100; LET q = x + y; -- q = 110 LET z = 'silly'; -- z receives a character value END LET y = x; -- y is now 5 LET x = z; -- z is now 15, not 'silly' END PROCEDURE; SPL Routine – Variables (cont’d) • Local variable scope – Example

30. SPL Routine – Variables (cont’d) • Global Variable • Is available to other SPL routines that are run by the same user session on the same database. • Requires a default value. • Must be defined in any SPL routine in which it is used. • Carries its value from one SPL routine to another until the session ends. • Cannot be a collection variable.

31. SPL Routine – Variables (cont’d) • Global Variable – Example CREATE FUNCTION func2()RETURNING INT; DEFINE GLOBAL gvar INT DEFAULT 5; LET gvar = gvar + 1; RETURN gvar; END FUNCTION; CREATE FUNCTION func1() RETURNING INT; DEFINE GLOBAL gvar INT DEFAULT 2; LET gvar = gvar + 1; RETURN gvar; END FUNCTION; EXECUTE FUNCTION func1(); EXECUTE FUNCTION func2(); gvar: 3 gvar: 4 gvar: 6 gvar: 7 EXECUTE FUNCTION func2(); EXECUTE FUNCTION func1();

32. SPL Routine – Assign Variables • Using a variable with undefined value receives error. • Assign value to defined variables: • LET statement LET <variables> = <valid expression or function name>; • SELECT INTO statement SELECT … INTO <variables> FROM …; • CALL … RETURNING statement CALL … RETURNING <variables>; • EXECUTE PROCEDURE/FUNCTION INTO statement EXECUTE PROCEDURE … INTO <variables>;

33. SPL Routine – Flow Control • Logic • IF-THEN-ELSE statement • CASE statement IF ( condition ) THEN statements ELIF ( condition ) THEN statements … ELSE statements END IF; CASE ( condition ) WHEN <value1> THEN statements WHEN <value2> THEN statements … ELSE statements END CASE;

34. SPL Routine – Flow Control (cont’d) • Loop – FOR statement FOR variable IN ( range or values ) or FOR variable = range • Range can have a STEP value. • Multiple ranges can be specified, separated by a comma. • Can use SELECT statement or function call to provide values. (Must return only one row).

35. SPL Routine – Flow Control (cont’d) • Loop – FOR statement (cont’d) -- FOR statement Examples • FOR count = 2 TO 30 STEP 2 • FOR count IN ( 2, 5, 7, 9, 11 to 20) • FOR name IN ( “AMY”, “MAX”, (SELECT name FROM customer WHERE customer_num = 100)) • FOR count = 1 TO max_no

36. SPL Routine – Flow Control (cont’d) • Loop – WHILE statement WHILE( condition ) statements END WHILE; ----- Examples • WHILE ( count < 20 ) • WHILE ( status matches “A*” ) • WHILE ( EXISTS (SELECT name FROM customer WHERE cus_num = 100) ) • WHILE ( status IN (“A”, “I”, “D”) )

37. SPL Routine – Flow Control (cont’d) • Loop – FOREACH statement: • Used to retrieve multiple rows. • Opens a cursor. • The named cursor can be used with CURRENT OF to update or delete a row. -- Example 1 FOREACH SELECT salary INTO ind_sal FROM customer WHERE location = “NA” sum_sal += ind_sal; END FOREACH; RETURN sum_sal;

38. SPL Routine – Flow Control (cont’d) • Loop – FOREACH statement (cont’d) -- Example 2 FOREACH emp FOR SELECT id INTO emp_id FROM high_performer WHERE location = “NA” UPDATE employee SET salary = salary* (1 + inc_rate) WHERE CURRENT OF emp; END FOREACH;

39. SPL Routine – Flow Control (cont’d) • Loop Control – CONTINUE and EXIT statement • Used in the FOR, FOREACH and WHILE loops. • CONTINUE causes the routine to skip the rest of the statements in the loop and process the next iteration of the loop. • EXIT causes the routine to skip the rest of the statements in the loop and process the next statement after the END statement. • The type of loop (FOR, FOREACH, or WHILE) must be specified following CONTINUE and EXIT. • EXIT operates on the innermost loop specified.

40. SPL Routine – Flow Control (cont’d) • Loop Control – CONTINUE and EXIT statements (cont’d) FOR i = 1 TO 5 LET j = i; WHILE (j > 0) LET id = foo(j); IF (id = 5) THEN LET j = j – 1; CONTINUE WHILE; -- skips the rest of statements in WHILE block END IF; LET sum = sum + 5; LET j = j – 1; IF (sum > 500) THEN EXIT FOR; -- exits the FOR loop and pass control to RETURN END IF; END WHILE; END FOR; RETURN sum;

41. SPL Routine – Returning Values • Use RETURN statement to pass values back. • Return values must match the type and order defined in RETURNING clause. • WITH RESUME keyword: -- The routine resumes at the statement after the RETURN WITH RESUME statement.

42. SPL Routine – Returning Values (cont’d) CREATE FUNCTION foo(a int) RETURNING CHAR(20) IF ( a = 5 ) THEN RETURN “HELLO” WITH RESUME; ELSE RETURN “HELLO”; END IF; RETURN “world”; END FUNCTION; HELLO EXECUTE FUNCTION foo(1); HELLO WORLD EXECUTE FUNCTION foo(5);

43. SPL Routine – Exception Handling • ON EXCEPTION statement • Provides error-trapping and error-recovery mechanism: • Specify errors to trap with IN clause. • Specify actions to take if the error occurs. • Allows multiple ON EXCEPTION statements in one statement block. • Must be defined after DEFINE statements and before any executable statement in a statement block. • Valid in nested statement blocks. • Use SET clause to receive SQL error code, ISAM error code and error message.

44. SPL Routine – Exception Handling (cont’d) -- Example CREATE PROCEDURE ex_test() DEFINE sql_err INTEGER; DEFINE isam_err INTEGER; DEFINE err_txt CHAR(200); ON EXCEPTION IN (-206) SET sql_err, isam_err, err_txt CREATE TABLE tab1 ( col1 INT, col2 INT); -- creates tab1 and return END EXCEPTION INSERT INTO tab1 VALUES (1, 2); -- tab1 doesn’t exist, go to exception handling INSERT INTO tab1 VALUES (2, 3);

45. SPL Routine – Exception Handling (cont’d) • WITH RESUME - Resume at the statement after the one that produces the error. CREATE PROCEDURE ex_test() ON EXCEPTION IN (-206) CREATE TABLE tab1 ( col1 INT, col2 INT); INSERT INTO tab1 VALUES (1, 2); END EXCEPTION WITH RESUME; INSERT INTO tab1 VALUES (1, 2); -- tab1 doesn’t exist, go to exception handling INSERT INTO tab1 VALUES (2, 3); -- resume here after exception handling INSERT INTO tab1 VALUES (3, 4); END PROCEDURE;

46. SPL Routine – Exception Handling (cont’d) • RAISE EXCEPTION statement: • Generates error. • Can specify SQL error and, optionally, ISAM error and error message. • The generated error can be caught by ON EXCEPTION. • Use special error number -746 to produce a customized message.

47. SPL Routine – Exception Handling (cont’d) -- Example CREATE PROCEDURE ex_test ( a INT ) ON EXCEPTION IN (-206) SET sql_err, isam_err, err_txt CREATE TABLE tab1 ( col1 INT, col2 INT); RAISE EXCEPTION sql_err, isam_err, err_txt; END EXCEPTION; IF (a < 1) THEN RAISE EXCEPTION -746, 0, “Insert value must be greater than 0”; END IF; INSERT INTO tab1 VALUES (1, 2);-- if tab1 doesn’t exist, go to exception handling END PROCEDURE;

48. SPL Routine - Tracing • Use SET DEBUG FILE statement to identify the trace file that receives the runtime trace output of a SPL routine. • Use TRACE statement to trace the execution of the routine: • TRACE ON: trace everything (statements, function calls, variables, returned values). • TRACE OFF: turn all tracing off. • TRACE PROCEDURE: trace routine calls. • TRACE <expression>: output the expression to trace file.

49. SPL Routine – Tracing (cont’d) Trace File output: trace on expression: (select (count *) from tab1) evaluates to 3 ; let count = 3 expression:count evaluates to 3 procedure test_trace returns 3 iteration of cursory procedure test_trace CREATE FUNCTION test_trace () RETURNING INT DEFINE count, a INT; LET count = 0; SET DEBUG FILE TO "/tmp/trace_out"; TRACE ON; LET count = (SELECT count(*) FROM tab1); RETURN count; END FUNCTION;

50. Execute SPL Routine • SPL Routines can be executed in many ways: • EXECUTE PROCEDURE and EXECUTE FUNCTION. • CALL statement to execute a SPL routine from another SPL routine. • Use routine name with an expression in an SQL statement.