Creating Trigger and Maintaining Update Log with PL/SQL

1. Creating Trigger and Maintaining Update Log with PL/SQL ISYS 650

2. Introduction to PL/SQL • Pl/SQL Block-Structured • Variable declaration section • Executable section • Exception-handling section

3. Variable Declaration • Variable name: begin with a letter and up to 30 characters. • Data types: • Character: CHAR(n), VARCHAR2(n) • Number: INTEGER, INT, SMALLINT • DECIMAL(i,j), NUMBER(i,j) • Boolean • Date

4. Examples • DECLARE Eid CHAR(3); Ename VARCHAR2(25); HireDate DATE DEFAULT SYSDATE; Salary NUMBER(7,2); TaxRate NUMBER(4,2) :=5.25;

5. %TYPE • Use a column’s data type as the data type for the variable: • CustomerName Customer.Cname%TYPE;

6. Executable Section • BEGIN • Statements • END; • / • “/ “ signals the end of program.

7. Assignment Statement • Tax := Salary * TaxRate; • Arithemetic operators: • +, -, *, /, ** • Concatenation: • || • FullName := (FirstName || ‘ ‘ || LastName);

8. Substitution Variables • &variableName • Ex. &Salary • On screen you will see: • Enter value for Salary: • Select * from Customer • Where CID=‘&CID’; • Ex1: Get input from screen: • Salary := &Salary;

9. SELECT … INTO • SELECT columns separated by commas • INTO variables separated by commas • FROM tablename • WHERE condition; • Ex1: • SELECT cid, cname INTO custID, customername • FROM customer • WHERE cid = ‘c01’; • Ex2: Using substituion variable • SELECT cid, cname INTO custID, customername • FROM customer • WHERE cid = ‘&cid’;

10. Decisions • IF condition THEN • Statements /* each statement ends with ; */ • END IF; • IF … THEN … ELSE …END IF; • IF … THEN ELSIF ……ELSE … END IF;

11. Loop • WHILE condition LOOP • Statements • END LOOP; • FOR i IN 1 .. 10 LOOP • Statements • END LOOP

12. Displaying Output • SET SERVEROUTPUT ON; • DBMS_OUTPUT.PUT_LINE(string to print); • DBMS_OUTPUT is a build in package, PUT_LINE is a procedure in that package. • Ex. • Tax:=Salary * TaxRate; • DBMS_OUTPUT.PUT_LINE(‘Your tax is: ‘ || Tax);

13. Script: Caltax.sql declare taxRate number(3,2); eid employees.employee_id%type; empSalary employees.salary%type; tax number(7,2); begin taxRate :=&taxRate; select salary into empSalary from employees where employee_id = '&eid'; tax := empSalary * taxRate; dbms_output.put_line('Tax is : ' || tax); end; /

14. Exceptions • Exceptions are errors. • Predefined exceptions: • No_Data_Found: Select statement returns no row. • Too_Many_Rows: Single-Row Select statement returns more than one row. • Value_Error: Data conversion errors.

15. Handling Exceptions • Exception • WHEN exceptionname THEN • Statements • WHEN OTHERS THEN • Statements • Ex: PLSQLException.SQL Declare CustName Customer.Cname%Type; begin select Cname into Custname from customer where cid='&CID'; dbms_output.put_line('Customer name is : ' || Custname); exception when no_data_found then dbms_output.put_line('Customer record not exist'); end; /

16. Functions CREATE [OR REPLACE] FUNCTION functionname IS [Variable declarations] BEGIN Statements RETURN expression; END; /

17. create or replace function emptax(sal employees.salary%type) return number is tax number(8,2); begin if sal < 2000.0 then tax:=sal*0.1; elsif sal <4000.0 then tax:=sal*0.2; else tax:=sal*0.3; end if; return tax; end; /

18. Using the User-defined Function with SQL • Select employee_id,first_name || ' ' || last_name as Ename, empTax(Salary) as Tax • FROM employees;

19. Creating Stored Procedure • A procedure is a named program block: CREATE [OR REPLACE] PROCEDURE procedurename IS [Variable declarations] BEGIN Statements END; / Note: To run a procedure: EXECUTE procedurename

20. create or replace procedure addCustomer ( custid customer.cid%TYPE, cname customer.cname%TYPE, city customer.city%TYPE, rating customer.rating%TYPE) is tempid customer.cid%TYPE; begin select cid into tempid from customer where cid=custid; dbms_output.put_line('Customer record already exist'); exception when no_data_found then insert into customer values(custid,cname,city,rating); end; / Note: To run a stored procedure: execute addcustomer('C12','Mia','LA','C');

21. Create a procedure to Issue Refresh Command create or replace procedure RefreshMVCust as begin DBMS_MVIEW.REFRESH('MVCustomer'); end; / Note: A stored procedure can be called from an application.

22. Triggers • A trigger is a program stored in the database and is called automatically when a triggering event occurs. • Update events: • Insert, Delete, Update • BEFORE Insert|Delete|Update ON tablename • AFTER Insert|Delete|Update ON tablename • Accessing new value and old value: • :NEW.fieldname • :OLD.fieldname • FOR EACH ROW

23. Demo :New and :Old create or replace trigger changesal after update on employees for each row begin dbms_output.put_line(:old.Last_name || ' old salary is:' || :old.salary); dbms_output.put_line('new salary is: ' || :new.salary); end; / Note: EmpSaltrigger.sql

24. create or replace trigger adddetail before insert on orderdetail for each row declare stocks product.onhand%type; begin select onhand into stocks from hproduct where pid=:new.pid; if stocks < :new.qty then update product set onhand=0 where pid = :new.pid; dbms_output.put_line('not enough stock'); else update product set onhand=onhand - :new.qty where pid=:new.pid; end if; end; / Note: AddDetailTrigger.sql

25. Master Table and User-Defined Log

26. Log Maintenance • Log structure: • Record’s fields + Update Time + Update Flag • Update Flags: • Deletion: D • Insertion: I • Modification is treated as deletion of the old record with slightly smaller update time and insert of the new record.

27. Code for Update (Modification) CREATE OR REPLACE TRIGGER "UpdateCustTrigger" after Update on Customer for each row declare UpdTimeStamp CustLog.updatetime%type; begin UpdTimeStamp:=sysdate-0.00005; insert into CustLog values(:old.cid,:old.Cname,:old.City,:old.Rating,UpdTimeStamp,'D'); UpdTimeStamp:=sysdate; insert into CustLog values(:new.cid,:new.Cname,:new.City,:new.Rating,UpdTimeStamp,'I'); end; /

28. Code for Insertion CREATE OR REPLACE TRIGGER "InsertCustTrigger" after Insert on Customer for each row declare UpdTimeStamp CustLog.updtime%type; begin UpdTimeStamp:=sysdate; insert into CustLog values(:new.cid,:new.Cname,:new.City,:new.Rating,UpdTimeStamp,'I'); end; /

29. Code for Deletion CREATE OR REPLACE TRIGGER "DeleteCustTrigger" after Delete on Customer for each row declare UpdTimeStamp CustLog.updtime%type; begin UpdTimeStamp:=sysdate; insert into CustLog values(:old.cid,:old.Cname,:old.City,:old.Rating,UpdTimeStamp,'D'); end; /

30. Patterns of Update Flag for an Updated Record • D, {I, D}: Deletion of an record • I, {D, I}: A new record • {D, I}: Modification • {I, D}: A new record eventually deleted (No impact to MV)

31. To Select All Deletions from the Log select * from custlog c where updateFlag='D' and updateTime=(select max(updateTime)from custLog where cid=c.cid); Note: For an updated record, select the last entry (the entry with Max(updateTime)) in the log with a Flag of D

32. To Select All Insertions from the Log select * from custlog c where updateFlag='I' and updateTime=(select max(updateTime)from custLog where cid=c.cid); Note: For an updated record, select the last entry (the entry with Max(updateTime)) in the log with a Flag of I

33. Demo: Remotely call a stored procedure to refresh a MV Dim strConn As String = "Provider=MSDAORA;Data Source=XE;User ID=dchao;password=???" Dim objConn As New OleDbConnection(strConn) Dim objcomm As New OleDbCommand("RefreshMVCust", objConn) objcomm.CommandType = CommandType.StoredProcedure objConn.Open() objcomm.ExecuteNonQuery() objConn.Close()