Program 4
4. Create a row level trigger for the customer table that would fire for INSERT or UPDATE, or
DELETE operations performed on the CUSTOMER123 table. This trigger will display the
salary difference between the old and new salary of CUSTOMER123(ID, NAME, AGE,
ADDRESS, Salary)
SQL> connect
Enter user-name: system
Enter password:
Connected.
SQL> create table customer123
2 (
3 id int,
4 name varchar(20),
5 age int,
6 address varchar(20),
7 salary decimal(10,2)
8 );
Table created.
SQL> desc customer123;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)
NAME VARCHAR2(20)
AGE VARCHAR2(20)
ADDRESS NUMBER(38)
SALARY NUMBER(38)
SQL> insert into customer123 values('&id','&name','&age','&address','&salary');
Enter value for id: 1
Enter value for name: Alive
Enter value for age: 24
Enter value for address: Khamma M
Enter value for salary: 20000
old 1: insert into customer123 values('&id','&name','&age','&address','&salary')
new 1: insert into customer123 values('1','Alive','24','Khamma M','20000')
1 row created.
SQL> /
Enter value for id: 2
Enter value for name: Bob
Enter value for age: 27
Enter value for address: Kadappa
Enter value for salary: 30000
old 1: insert into customer123 values('&id','&name','&age','&address','&salary')
new 1: insert into customer123 values('2','Bob','27','Kadappa','30000')
1 row created.
SQL> /
Enter value for id: 3
Enter value for name: Catri
Enter value for age: 25
Enter value for address: Guntur
Enter value for salary: 40000
old 1: insert into customer123 values('&id','&name','&age','&address','&salary')
new 1: insert into customer123 values('3','Catri','25','Guntur','40000')
1 row created.
SQL> /
Enter value for id: 4
Enter value for name: Dena
Enter value for age: 28
Enter value for address: Hyderabad
Enter value for salary: 50000
old 1: insert into customer123 values('&id','&name','&age','&address','&salary')
new 1: insert into customer123 values('4','Dena','28','Hyderabad','50000')
1 row created.
SQL> /
Enter value for id: 5
Enter value for name: Eshu
Enter value for age: 27
Enter value for address: Kurnool
Enter value for salary: 60000
old 1: insert into customer123 values('&id','&name','&age','&address','&salary')
new 1: insert into customer123 values('5','Eshu','27','Kurnool','60000')
1 row created.
SQL> /
Enter value for id: 6
Enter value for name: Farooq
Enter value for age: 28
Enter value for address: Nellur
Enter value for salary: 70000
old 1: insert into customer123 values('&id','&name','&age','&address','&salary')
new 1: insert into customer123 values('6','Farooq','28','Nellur','70000')
1 row created.
SQL> select *from customer123;
ID NAME AGE ADDRESS SALARY
---------- -------------------- ---------- -------------------- ----------
1 Alive 24 Khamma M 20000
2 Bob 27 Kadappa 30000
3 Catri 25 Guntur 40000
4 Dena 28 Hyderabad 50000
5 Eshu 27 Kurnool 60000
6 Farooq 28 Nellur 70000
6 rows selected.
CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON CUSTOMER123
FOR EACH ROW
DECLARE
Sal_diff NUMBER;
BEGIN
IF INSERTING THEN
dbms_output.put_line('Inserting new row...');
dbms_output.put_line('New salary: ' || :NEW.salary);
ELSIF DELETING THEN
dbms_output.put_line('Deleting row...');
dbms_output.put_line('Old salary: ' || :OLD.salary);
ELSIF UPDATING THEN
Sal_diff := :NEW.salary - :OLD.salary;
dbms_output.put_line('Updating row...');
dbms_output.put_line('Old salary: ' || :OLD.salary);
dbms_output.put_line('New salary: ' || :NEW.salary);
dbms_output.put_line('Salary difference: ' || Sal_diff);
END IF;
END;
/
Trigger created
SQL> insert into customer123 ('&id','&name','&age','&address','&salary');
2 values (‘7’,‘Kriti’,’42’,’HP’, ‘7500’);
1 row created
SQL> select *from customer123
ID NAME AGE ADDRESS SALARY
---------- -------------------- ---------- -------------------- ----------
1 Alive 24 Khamma M 20000
2 Bob 27 Kadappa 30000
3 Catri 25 Guntur 40000
4 Dena 28 Hyderabad 50000
5 Eshu 27 Kurnool 60000
6 Farooq 28 Nellur 70000
7 Kriti 42 HP 7500
1 row updated
SQL> update customer123
2 set salary =salary+500
3 where id=2;
SQL> select *from customer123;
ID NAME AGE ADDRESS SALARY
---------- -------------------- ---------- -------------------- ----------
1 Alive 24 Khamma M 20000
2 Bob 27 Kadappa 30500
3 Catri 25 Guntur 40000
4 Dena 28 Hyderabad 50000
5 Eshu 27 Kurnool 60000
6 Farooq 28 Nellur 70000
7 Kriti 42 HP 7500
7 rows selected
SQL> commit;
Commit completed
SQl>Exit
