RDBMS SOLUTION


ASSIGNMENT 6 

SET A Q. 3 SOLUTION

Write a trigger which will fire before insert or update on Investment having investment date greater than current date. (Raise user defined exception and give appropriate message)




Source code:

    

    /*set a q 3 solution

    Explaination of the code at the end*/

Create or replace trigger t3 before insert or update on investment for each row

declare

t3 exception;

current_date date:= SYSDATE;

Begin

if (:new.idate > current_date) then

raise t3;

end if;

Exception

when t3 then 

dbms_output.put_line('Date should be less then todays date');

end;

insert into investment values(13,'I7',to_date('05/11/2023','dd/mm/yyyy'),20000)

/* Explaination:

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

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

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

- "current_date date:= SYSDATE;" - this line declares a variable named "current_date" and initializes it with the current system date and time using the SYSDATE function.

- "if (:new.idate > current_date) then" - this line checks if the value of "idate" in the current row is greater than the "current_date" value. If it is, then the "t3" exception is raised.

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

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

- "dbms_output.put_line('Date should be less then todays date');" - this line prints a message to the console indicating that the value of "idate" is not valid.

In summary, this trigger is used to validate the "idate" column in the "investment" table to ensure that the date is not in the future. If the date is in the future, 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.

 */
 Download code         next