Stored Procedures and Triggers

This tutorial steps through the topics at right to cover two advanced SQL features that are supported when accessing Actian Zen with ODBC:

• Stored procedures
• SQL triggers

Stored procedures are a kind of saved SQL statement.

SQL triggers are a special type of stored procedure that executes automatically when a data modification event occurs a table.

Note that the examples in this tutorial use the Students table created in the ZENDB database with the data that was inserted throughout the previous tutorial topics.

Setting Up

If you have been following the series, your current table looks like this:

(1, 'John', 100, 'English')
(2, 'Mary', 200, 'English')
(3, 'Bob', 300, 'English')
(4, 'Amy', 400, 'English')
(5, 'Sam', 500, 'English')
(6, 'Jill', 600, 'English')

We’ll use this data set for the lessons in this tutorial.

Create a Stored Procedure to Insert New Students

A stored procedure is a saved SQL statement that we can execute at any time. A stored procedure is like a view in the sense that we simply call it by name rather than the entire statement, but unlike a view, a stored procedure can include most SQL statements to operate on records or tables in the database.

We use a CREATE PROCEDURE statement to create a simple stored procedure that adds a student and account balance:

procedure_query="CREATE PROCEDURE Add_Student(); \
                          BEGIN \
                          INSERT INTO Students(Name, Account_Balance) \
                          VALUES ('John Doe', 0); \
                          END"
c.execute(procedure_query)

CREATE PROCEDURE defines the name of the procedure to add a student. The BEGIN and END clauses define the start and end of the procedure itself, which we recognize as a standard INSERT statement.

This will create a new procedure Add_Student in the Actian Zen database that we can execute anytime to add a new student. We can see it in Zen Control Center under Stored Procedures:

To execute the procedure:

c.execute("CALL Add_Student()")

If we look at the Students table again we can see that a new record was inserted:

Create a Stored Procedure that Takes Parameters

There’s one problem with the previous example: It inserts a record for the same John Doe every time we call Add_Student().

It would be more useful if we could tell the procedure who to create a record for and how much to register for the account balance. To do that, we can create a stored procedure that takes parameters. The parameters in a stored procedure enable the providing of specific data for the procedure to use at execution time.

We use the CREATE PROCEDURE statement to create a new stored procedure that takes variables or parameters for the Name and Account_Balance columns:

procedure_query="CREATE PROCEDURE Add_Student2 \ 
                          (in :Name CHAR(32), in :Amount INTEGER); \
                          BEGIN \ 
                          INSERT INTO Students(Name, Account_Balance) \ 
                          VALUES (:Name, :Amount); \
                          END"
c.execute(procedure_query)

Note that in this case, “(in :Name CHAR(32), in :Amount INTEGER)” specifies the two parameters expected by the procedure.

To execute it using the name Mary Jane and an account balance of 500, we run this command:

c.execute("CALL Add_Student2('Mary Jane', 500)")

If you look at the Students table:

More Information:

• Stored Procedures

Create a SQL Trigger to Increase Account Balance

SQL triggers are stored procedures that execute before or after a SQL statement is executed on a specified table.

We use the CREATE TRIGGER statement to start the SQL trigger, but notice that the rest of the statement uses some new clauses.

Here is a simple example of creating a trigger that adds 100 to the account balance of every new Student record:

trigger_query="CREATE TRIGGER Add_Balance \AFTER INSERT ON Students \REFERENCING NEW AS student \FOR EACH ROW \UPDATE Students SET \Account_Balance = student.Account_Balance + 100) \WHERE Id = student.Id;"

c.execute(trigger_query)

In this query, we created a new Trigger called Add_Balance that will execute a SQL query after a new row is inserted into the Students table. The new record values will be represented as a student variable, and for each row (record) created, the trigger will execute UPDATE to increase the current Account_Balance value by 100.

We can find the new trigger under Triggers in Zen Control Center:

To see the trigger in action, insert a new record:

c.execute("INSERT INTO Students(Name, Account_Balance) VALUES ('Bill', 800)")

This is the result we’ll see:

Notice that even though we specified 800 as the Account_Balance, we ended up with 900 because the trigger automatically added 100 after the INSERT operation.

More information:

• SQL Triggers