KEMBAR78
DBMS Experiment 9 | PDF | Data Management Software | Computing
Scribd
Upload
40 views3 pages

DBMS Experiment 9

The document provides the syntax for creating a trigger in SQL, specifically for a CUSTOMERS table. An example trigger is shown that calculates and displays the salary difference during INSERT, UPDATE, or DELETE operations. Additionally, it includes a procedure to update salaries and demonstrate the trigger's output, confirming that 6 customers were updated.

Uploaded by

skgomango22
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
40 views3 pages

DBMS Experiment 9

The document provides the syntax for creating a trigger in SQL, specifically for a CUSTOMERS table. An example trigger is shown that calculates and displays the salary difference during INSERT, UPDATE, or DELETE operations. Additionally, it includes a procedure to update salaries and demonstrate the trigger's output, confirming that 6 customers were updated.

Uploaded by

skgomango22
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

Creating a trigger:

Syntax for creating trigger:

CREATE [OR REPLACE ] TRIGGER trigger_name


{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;

EXAMPLE:

In this example, we are using the following CUSTOMERS table:

ID NAME AGE ADDRESS SALARY

1 Ramesh 23 Allahabad 20000

2 Suresh 22 Kanpur 22000

3 Mahesh 24 Ghaziabad 24000

4 Chandan 25 Noida 26000

5 Alex 21 Paris 28000

6 Sunita 20 Delhi 30000

Create trigger:

Let's take a program to create a row level trigger for the CUSTOMERS table that would fire for
INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This trigger will
display the salary difference between the old values and new values:
1. CREATE OR REPLACE TRIGGER display_salary_changes
2. BEFORE DELETE OR INSERT OR UPDATE ON customers
3. FOR EACH ROW
4. WHEN (NEW.ID > 0)
5. DECLARE
6. sal_diff number;
7. BEGIN
8. sal_diff := :NEW.salary - :OLD.salary;
9. dbms_output.put_line('Old salary: ' || :OLD.salary);
10. dbms_output.put_line('New salary: ' || :NEW.salary);
11. dbms_output.put_line('Salary difference: ' || sal_diff);
12. END;

RESULT :
Trigger created.

Check the salary difference by procedure:

Use the following code to get the old salary, new salary and salary difference after the trigger
created.

1. DECLARE
2. total_rows number(2);
3. BEGIN
4. UPDATE customers
5. SET salary = salary + 5000;
6. IF sql%notfound THEN
7. dbms_output.put_line('no customers updated');
8. ELSIF sql%found THEN
9. total_rows := sql%rowcount;
10. dbms_output.put_line( total_rows || ' customers updated ');
11. END IF;
12. END;

OUTPUT:

Old salary: 20000


New salary: 25000
Salary difference: 5000
Old salary: 22000
New salary: 27000
Salary difference: 5000
Old salary: 24000
New salary: 29000
Salary difference: 5000
Old salary: 26000
New salary: 31000
Salary difference: 5000
Old salary: 28000
New salary: 33000
Salary difference: 5000
Old salary: 30000
New salary: 35000
Salary difference: 5000
6 customers updated

You might also like