Write a trigger which will fire before insert or update on Bill having day other than seven week days. (Raise user defined exception and give appropriate message)

RDBMS SOLUTION

ASSIGNMENT 6 

SET B Q.2 SOLUTION

Write a trigger which will fire before insert or update on Bill having day other than seven week days. (Raise user defined exception and give appropriate message)

Source code:

    

    /* set b q 2 solution*/

create or replace trigger t5

before insert or update

on bill

for each row

declare

v_number number;

t5 exception;

begin

v_number:= to_number(to_char(:new.bday,'d'));

if(v_number>=6) then

raise t5;

end if;

exception

when t5 then 

dbms_output.put_line('Bill can be inserted or updated only on non-weekends');

end;

/* Explaination:

- "Create or replace trigger t5" - this line indicates that a new trigger named "t5" should be created or replaced if it already exists.

- "before insert or update on bill for each row" - this line specifies that the trigger should execute before any row is inserted or updated in the "bill" table.

- ":new" - refers to the values being inserted or updated in the current row.

- "v_number:= to_number(to_char(:new.bday,'d'))" - this line converts the "bday" date value in the current row to a day of the week number (1 for Sunday, 2 for Monday, etc.) using the "to_char" and "to_number" functions.

- "if(v_number>=6) then" - this line checks if the day of the week number is greater than or equal to 6, which corresponds to Saturday or Sunday. If it is, then the "t5" exception is raised.

- "raise t5;" - this line raises the "t5" exception if the "if" condition is true.

- "Exception when t5 then" - this line specifies that if the "t5" exception is raised, the following code block should be executed.

- "dbms_output.put_line('Bill can be inserted or updated only on non-weekends');" - this line prints a message to the console indicating that the "bill" can only be inserted or updated on weekdays (non-weekends).

 */

   

    

 Download code         next