RDBMS SOLUTION


ASSIGNMENT 6 

SET A Q. 2 SOLUTION

Write a trigger which will fire before insert or update on Employee having Emp id less than equal to zero (Raise user defined exception and give appropriate message)




Source code:

    

    /*set a q 2 solution

    Explaination of the code at the end*/

create or replace trigger t2 before insert or update on employee for each row

declare

t2 exception;

begin

if(:new.eid<=0) then

raise t2;

end if;

exception

when t2 then 

dbms_output.put_line('Not valid Employee ID');

end;

insert into employee values(-3,'e4','a4')

/* Explaination:

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

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

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

- "if(:new.eid<=0) then" - this line checks if the value of "eid" in the current row is less than or equal to 0. If it is, then the "t2" exception is raised.

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

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

- "dbms_output.put_line('Not valid Employee ID');" - this line prints a message to the console indicating that the value of "eid" is not valid.

- "insert into employee values(-3,'e4','a4')" - this line is an "insert" statement that tries to insert a row with an invalid "eid" value (-3) into the "employee" table. Since the "eid" value is less than or equal to 0, the trigger is triggered, and an exception is raised. The "insert" statement will not be executed successfully.

 */
 Download code         next