RDBMS SOLUTION
ASSIGNMENT 6
SET B Q.1 SOLUTION
Write a trigger which will fire before insert or update on Menu having price less than or equal to zero. (Raise user defined exception and give appropriate message)
Source code:
/*Assignment 6 set b q 1 solution
first we will create tables and then we will create triggers*/
create table bill
(
bno number not null primary key,
bday varchar2(30),
tableno number,
total number
)
create table menu
(
dno number not null primary key,
dish_desc varchar2(40),
price number
)
create table b_m
(
bno number references bill(bno),
mno number references menu(dno)
)
/*trigger*/
create or replace trigger t4 before insert or update on menu for each row
declare
t4 exception;
Begin
if(:new.price <= 0) then
raise t4;
end if;
Exception
when t4 then
dbms_output.put_line('Price must be Greater then 0');
end;
/* Explaination:
- "Create or replace trigger t4" - this line indicates that a new trigger named "t4" should be created or replaced if it already exists.
- "before insert or update on menu for each row" - this line specifies that the trigger should execute before any row is inserted or updated in the "menu" table.
- ":new" - refers to the values being inserted or updated in the current row.
- "if(:new.price <= 0) then" - this line checks if the value of "price" in the current row is less than or equal to 0. If it is, then the "t4" exception is raised.
- "raise t4;" - this line raises the "t4" exception if the "if" condition is true.
- "Exception when t4 then" - this line specifies that if the "t4" exception is raised, the following code block should be executed.
- "dbms_output.put_line('Price must be Greater then 0');" - this line prints a message to the console indicating that the value of "price" is not valid.
In summary, this trigger is used to validate the "price" column in the "menu" table to ensure that it is not less than or equal to 0. If the price is not valid, an exception is raised and a message is printed to the console. This helps ensure that only valid data is inserted or updated in the table.
*/
0 Comments