CS 328 - Week 1 Labs - 1:00 pm Lab - 2016-01-22

*   PL/SQL - an extension of SQL from Oracle
    *   it is Oracle's "procedural language" extension of SQL

*   so, DO need to keep straight:
    *   "plain" SQL commands
    *   SQL*Plus commands
    *   PL/SQL (which can contain many of the "plain" SQL cmds)

*   trigger: one of the types of PL/SQL subroutines
    *   procedural code stored in the database that is
        executed when a particular insert, update, or delete
	(IUD) occurs

    *   its primary is to enhance database integrity

*   basic structure of a trigger:

    [ ] - optional
    < > - an identifier of your choice
    { } - choose from the contents of the { }

    create [or replace] trigger <trigger_name> 
        {before | after} {delete | insert | update}
        on <table_name>
        [for each row]
    [declare
        ]
    begin
        [statements]
    [exception
        ]
    end;
    /      /* DON'T FORGET THIS !!!!!!!!!!!!!!! */

    show errors

*   another cool thing in declaring PL/SQL
    local variables:
    *   you can declare a local variable to have
        the same type as a database table's column
	as so:

	var_name tblname.colname%TYPE;

        item_ordered inventory.item_num%TYPE;

    *   you can grab a single value from the row
        being newly inserted or the result after
	being newly modified with
	
	:new.colname

    *   you can grab a single value from the row
        being deleted or the result before
	being  modified with:

	:old.colname

*   if syntax in PL/SQL:

    if <condition> then
        [statements]
    [else
        [statements]]
    end if;

*   you can specify in a PL/SQL subroutine to print
    to the screen using:

    dbms_output.put_line('desired string');

    BUT you will ONLY SEE this output IF you set
    SQL*Plus's serveroutput to be on!!!!
    (by default, it is off)

    set serveroutput on