DBMS Lab Cycle 8 (Triggers)

--> Create the table STORE, with the fields as given CODE VARCHAR2(15), NAME VARCHAR2(15), QTY NUMBER, PRICE NUMBER Create procedures to perform the following actions into the table STORE.

CREATE TABLE store(
code VARCHAR2(15),
name VARCHAR2(15),
qty NUMBER,
price NUMBER,
PRIMARY KEY(code)
);

• INSERT record using procedure

CREATE OR REPLACE PROCEDURE insertrow(code1 IN VARCHAR2,name1 IN VARCHAR2,qty1 NUMBER,price1 NUMBER) IS
BEGIN
INSERT INTO store VALUES(code1,name1,qty1,price1);
DBMS_OUTPUT.PUT_LINE('One row inserted');
END;
/

SET SERVEROUTPUT ON
DECLARE
code VARCHAR2(15);
name VARCHAR2(15);
qty NUMBER;
price NUMBER;
BEGIN
code:=&enter_the_code;
name:=&enter_the_name;
qty:=&enter_the_quantity;
price:=&enter_the_price;
insertrow(code,name,qty,price);
END;

• DELETE record

CREATE OR REPLACE PROCEDURE deleterow(code1 IN VARCHAR2) IS
BEGIN
DELETE FROM store WHERE code LIKE code1;
DBMS_OUTPUT.PUT_LINE('One row deleted');
END;
/

SET SERVEROUTPUT ON
DECLARE
code VARCHAR2(15);
BEGIN
code:=&enter_the_code_for_deletion;
deleterow(code);
END;

• UPDATE record

CREATE OR REPLACE PROCEDURE updaterow(code1 IN VARCHAR2,name1 IN VARCHAR2,qty1 NUMBER,price1 NUMBER) IS
BEGIN
UPDATE store SET name=name1,qty=qty1,price=price1 WHERE code = code1;
commit;
DBMS_OUTPUT.PUT_LINE('Details of item code ' || code1 ||' updated');
END;
/ 

---

--> Phone book table, with fields no int, name varchar(10),dno varchar(10),street varchar(10).

CREATE TABLE phone_book(
no NUMBER(10),
name VARCHAR2(15),
dno VARCHAR2(10),
street VARCHAR2(15),
PRIMARY KEY (no)
);

• Develop a function to search the ‘address’ from the phone book table

CREATE OR REPLACE FUNCTION searcher(no1 IN NUMBER) RETURN VARCHAR2 IS
address VARCHAR2(30);
BEGIN
SELECT name || ',' || dno || ',' || street INTO address  FROM phone_book WHERE no = no1;
--DBMS_OUTPUT.PUT_LINE('Address details of phone number' || no1 || 'is' || address);
RETURN address;
END;
/

SET SERVEROUTPUT ON
DECLARE
ph_num NUMBER(10);
res VARCHAR2(50);
BEGIN
ph_num:=&enter_the_Ph_no;
res := searcher(ph_num);
DBMS_OUTPUT.PUT_LINE(res);
END;
/

•  Create a function to update the details of the person with name ‘Hari’

CREATE OR REPLACE FUNCTION updaterhari(dno1 IN VARCHAR2, street1 IN VARCHAR2) RETURN NUMBER IS
address VARCHAR2(30);
BEGIN
UPDATE phone_book SET dno=dno1,street=street1 WHERE name = 'Hari';
commit;
DBMS_OUTPUT.PUT_LINE('Details of Hari Updated successfully');
RETURN 0;
END;
/

SET SERVEROUTPUT ON
DECLARE
   res number;
BEGIN
res:=updaterhari('dno1','street1');
END;
/  

---

--> Create a DML trigger that uses conditional predicates to determine which of its four possible triggering statements fired it :
A TRIGGER fired when an INSERT, UPDATE OF salary, title OR DELETE of rows is done on he table ‘emp’

CREATE TABLE emp( 
  empno NUMBER(4,0), 
  ename VARCHAR2(10), 
  sal NUMBER(7,2),
  title VARCHAR2(30),
  PRIMARY KEY(empno)
);

Insert into emp (EMPNO,ENAME,SAL,TITLE) values (1001,'Adharsh',30000,'Manager');
Insert into emp (EMPNO,ENAME,SAL,TITLE) values (1002,'Dimal',90000,'CFO');
Insert into emp (EMPNO,ENAME,SAL,TITLE) values (1003,'Visakh',25000,'Lead');

• WHEN INSERTING a row, the trigger should be triggered and should display as `inserting

CREATE OR REPLACE TRIGGER insertalert
AFTER INSERT ON emp
FOR EACH ROW
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('Inserting');
END IF;
END;
/

• WHEN UPDATING the ‘salary’, the trigger should be triggered and should display as Updating salary

CREATE OR REPLACE TRIGGER salaryupdate
AFTER UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Updating Salary');
END;
/

• WHEN UPDATING title the trigger should be triggered and should display as Updating title .

CREATE OR REPLACE TRIGGER titleupdate
AFTER UPDATE OF title ON emp
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Updating Title');
END;
/

• WHEN DELETING a row THEN the trigger should be triggered and should display as Deleting

 CREATE OR REPLACE TRIGGER deletealert
AFTER DELETE ON emp
FOR EACH ROW
BEGIN
IF DELETING THEN
DBMS_OUTPUT.PUT_LINE('Deleting');
END IF;
END;
/