OraFAQ Forum: SQL & PL/SQL » triggers in oracle plsql

Home » SQL & PL/SQL » SQL & PL/SQL » triggers in oracle plsql (windows xp)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

triggers in oracle plsql [message #573261]

Wed, 26 December 2012 01:49

nvsr
Messages: 49
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

Report message to a moderator

Re: triggers in oracle plsql [message #573264 is a reply to message #573261]

Wed, 26 December 2012 02:46

Michel Cadot
Messages: 54195
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

Report message to a moderator

Re: triggers in oracle plsql [message #573265 is a reply to message #573261]

Wed, 26 December 2012 02:48

Littlefoot
Messages: 16980
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;
/

Report message to a moderator

Re: triggers in oracle plsql [message #573305 is a reply to message #573265]

Wed, 26 December 2012 09:37

nvsr
Messages: 49
Registered: December 2011
Location: HYDERABAD

Member

thanks littlefoot ....

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 22 20:57:34 CDT 2013

Total time taken to generate the page: 0.19014 seconds