Posts

Triggers

1 Triggers Triggers are simply stored procedures that are ran automatically by the database whenever some event (usually a table update) happens. We won’t spend a great deal of time talking about how to write triggers, because if you know how to write stored procedures, you already know how to write triggers. What we will discuss is how to set them up to be executed whenever something happens to the table. 2 PL/SQL Triggers Triggers are basically PL/SQL procedures that are associated with tables, and are called whenever a certain modification (event) occurs. The modification statements may include INSERT, UPDATE, and DELETE. The general structure of triggers is: CREATE [OR REPLACE] TRIGGER trigger_name BEFORE (or AFTER) INSERT OR UPDATE [OF COLUMNS] OR DELETE ON tablename [FOR EACH ROW [WHEN (condition)]] BEGIN ... END; The usual CREATE OR REPLACE we have already seen with procedures and functions... TRIGGER specifies just what type of object we are creating. The
Post a Comment
Read more

cursors

Cursors In a previous lecture we saw how we can use SQL statements in PL/SQL. As you remember this way has a disadvantage, namely, all our statements should return only one row. This functionality is enhanced through the use of cursors, which allow a program to take explicit control of SQL statement processing. In order to process a SQL statement, Oracle will allocate an area of memory known as the context area . The context area contains information necessary to complete the processing (including the number of rows processed, a pointer to the parsed representation of the statement, set or rows returned by the query). A cursor is a handle, or pointer, to the context area. Through the cursor, a PL/SQL program can control the context area and what happens to it as a statement is processed. Processing explicit cursors The four steps necessary for explicit cursor processing are as follows: Declare the cursor. Open the cursor for a query.
Post a Comment
Read more