Home » SQL & PL/SQL » SQL & PL/SQL » triggers in oracle plsql (windows xp)
triggers in oracle plsql [message #573261] Wed, 26 December 2012 01:49 Go to next message
nvsr
Messages: 51
Registered: December 2011
Location: HYDERABAD
Member

Hai All...

i just created one trigger . this Purpose is Every month Lastday after 10'o Clock PM no DML Operations on table.
it is not working fine..
please help....



-------------------------------------------------
create or replace trigger mnth_lastday_trigr
before insert or update or delete
on table_name
for each row
declare

v_lastdaytime varchar2(100);
v_todaytime varchar2(100);

begin

v_lastdaytime := to_char(last_day(sysdate),'DD-MM-YYYY HH24:MI:SS');
v_todaytime := to_char(sysdate,'DD-MM-YYYY')||' '||'22:00:00';

if v_lastdaytime <= v_todaytime then

raise_application_error(-20100,'An Illegal Instruction into the System was Detected.');

end if;
end;



thanks for
Advance
Re: triggers in oracle plsql [message #573264 is a reply to message #573261] Wed, 26 December 2012 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59407
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does "it is not working fine.. " mean? It is surely not an Oracle error message (but I might be wrong).

Regards
Michel
Re: triggers in oracle plsql [message #573265 is a reply to message #573261] Wed, 26 December 2012 02:48 Go to previous messageGo to next message
Littlefoot
Messages: 19686
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Condition you talk about mentions "days", "hours", "every month" - all these are date values; so, why did you use VARCHAR2 variables? I've never seen that working with dates in a character format brings anything good, only problems.

Therefore: as you need to check whether "current date and time" is "after 22:00 on the last day of the month", you just need to convert these words into statement, such as
create or replace trigger trg_no_dml 
  before insert or update or delete
  on your_table
begin
  if sysdate > trunc(last_day(sysdate)) + 22/24
  then
     raise_application_error(-20001, 'Your message goes here');
  end if;
end;
/
Re: triggers in oracle plsql [message #573305 is a reply to message #573265] Wed, 26 December 2012 09:37 Go to previous message
nvsr
Messages: 51
Registered: December 2011
Location: HYDERABAD
Member

thanks littlefoot ....
Previous Topic: insert or update records into target table
Next Topic: Trigger fires on deletion of uncommitted data and changes saved
Goto Forum:
  


Current Time: Tue Oct 21 07:47:57 CDT 2014

Total time taken to generate the page: 0.24920 seconds