trigger not firing

Question

set serveroutput on;
create or replace trigger check_date
before insert 
on emp
for each row
begin
    dbms_output.put_line('test statement'||:new.hiredate||sysdate); --to test display
    if(:new.hiredate<sysdate) then
        dbms_output.put_line('invalid');
    end if;
end;
/


insert into emp(empno,deptno,hiredate) values(533,20,'23-3-12');

I have written the above trigger but when i try to insert a new record into emp table,the record is inserted without firing the trigger.

Instead of dbms_output.put_line use a table, say mytab inside the trigger. Insert your test output string into this table mytab. After your insert into emp query mytab to check the test output. — ArtBajji
@ArtBajji Thanks it worked.But I want to display the String to the user using 'dbms_output.put_line' — sai teja
You don't know that an end user will be able to see that though. It depends on the client, and then on settings. Why not raise an exception instead? (Also , '23-3-12' is a string, not a date; implicit conversion might get the value you want but isn't safe. Explicit conversion or a date literal is safer - e.g. DATE '2012-03-23') — Alex Poole
And, if you do just display that message, and even if the user does see it, the insert still succeeds. The user would have to see the message and choose to roll back; otherise you'd still get the invalid value in your table. Raising an exception rolls back the insert statement to prevent that (but not the whole transaction). — Alex Poole

Logesh Varathan Logesh Varathan · Accepted Answer · 2018-04-02T09:31:55

I have tried the same code which has been attached above. The only issue I faced is the date parameter. Before passing the date as you wish, please check the default date format via Tools->Preferences->Database->NLS->Date Format. Then pass the date as provided in the default format. If not the date format needs to be implicitly converted using to_date.

It will be working fine as you expected.

trigger not firing

1 Answers