PLSQL and the Table API Benefits of ServerSide

PL/SQL and the Table API

Benefits of Server-Side Code Speedy Pizza MENU PIZZA NAPOLITAINE Triggers Procedures Functions Packages • Reduced network traffic • Maintainability • Data integrity

Older Editions of Designer • Most constraints only enforced in application • Server vulnerable to other access methods • Application code references tables and generates network traffic

Database Trigger • NOT a form trigger – When-enter-block, When-validate-item… • • • Occurs in server Associated with Lock or DB modification Before or After DB event For every affected Row, or For SQL Statement

DML action What Is a Database Trigger? Trigger • When – Before DML ITEMS – After DML • What – Row – Statement Database Trigger Code that is implicitly executed when an INSERT, UPDATE, or DELETE statement is issued against its associated table or view

Triggers and Packages • Triggers often call procedures in Packages • Packages provide Scope for Variables shared by Procedures • Each process gets own copy of Package Variables

Packages RENTALS Public Definitions Body Procedures 1 +1 2 Specification Action Functions Public and Private Elements Value = 2

PL/SQL in the Repository Navigator - Server Model Distribution Implementation APPLICATION[1] Relational Table Definitions PL/SQL Composition Triggers Functions Procedures Triggers Structure Packages

PL/SQL Definitions Server Model PL/SQL Definitions Function Definitions Package Definitions Procedure Definitions P_SET_PRICE Arguments Sub Program Units Program Datastructures Synonyms Cursor Definitions Trigger Definitions Undefined PL/SQL CREATE. . . ( p_name IN VARCHAR 2) IS v_date DATE; TYPE table_type IS. . . BEGIN IF. . . THEN. . . ; . . . callprocedure(v_date); . . . END;

Defining Triggers Navigator - Server Model APPLICATION[1] Relational Table Definitions TITLES Triggers TRIG 1 Columns Trigger Header Definition PL/SQL Definition Complete Enabled Trigger. . . When Condition Trigger Logic TRIG 1 PL/SQL Definitions PL/SQL Trigger. Composition Definitions PL/SQL TRIG 1 . . . PL/SQL Block. . . PL/SQL Logic

Opening the Logic Editor • Drag the definition to the work surface Navigator - Server Model PL/SQL Definitions Procedure Definitions P_SET_PRICE

Using the Logic Editor PL/SQL PROCEDURE Select SELECT INTO FROM WHERE Outliner SELECT title Text Editor INTO v_title FROM titles WHERE product_code = PL/SQL p_product_code; . . . Construct Root Constructs Statements Static Data Tree

Generating PL/SQL Objects DDL file: • CREATE OR REPLACE statements • Header • Data declarations • PL/SQL logic Use the utility as you would for other database objects

Fortunately. . . • You do not have to write most triggers or packages • Designer writes them for you • You give specifications • You generate Table API • All this code used to be in Applications

Table API Keeps Applications Thin Application Table API Tables Validation code

Table API Triggers • Fire on insert, update, or delete • Call the server packages Triggers

Table API Packages • Are called by: – Applications – Triggers • Perform DML operations • Validate data • Derive column values Serv Pack er ages

Server Packages Package cg$titles call cg$titles. ins procedures. . . Insert TITLES # * PROD_CDE * TITLE. . . Update Delete Lock trigger Insert

Why API? • Table API provides a Table Handler for each package • Normal DML causes Triggers to Invoke Handlers • Applications can call API procedures directly – Disabling recursive call of trigger

Applications and the API Triggers Form Builder application TABLE Column_1 Column_2 er v r e S s e g a Pack Web. Server application

Customization • API handles many standard Requirements. • You can add code to API for special Requirements. • Even specify whether it is called before or after standard handling.