Home » SQL & PL/SQL » SQL & PL/SQL » Timebound trigger
icon14.gif  Timebound trigger [message #201408] Sat, 04 November 2006 02:08 Go to next message
pal4you
Messages: 1
Registered: November 2006
Junior Member
Hi!
Can sombody help me in writing a trigger / function which will get fired/activated on perticular date.
Re: Timebound trigger [message #201413 is a reply to message #201408] Sat, 04 November 2006 02:23 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Automatic Fire := used trigger.
Return value := Function.

Simple Example.
1. Trigger.
SQL> create table a ( no number, name varchar2(20));

Table created.

SQL> create table a_history as select * from a;

Table created.

SQL> begin
  2  insert into a values ( 1, 'a');
  3  insert into a values ( 2,'b');
  4  insert into a values (3,'c');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> create or replace trigger a_history
  2  after delete on a
  3  for each row
  4  begin
  5  insert into a_history values ( :old.no,:old.name);
  6  end;
  7  /

Trigger created.

SQL> delete a where no = 1;

1 row deleted.

SQL> select * from a_history;

        NO NAME
---------- --------------------
         1 a

SQL> select * from a;

        NO NAME
---------- --------------------
         2 b
         3 c

SQL>

Note : So Many Features with Triggers plz. go through documentation.

2.Function
SQL> ed
Wrote file afiedt.buf

  1  create or replace function tax_fun (amount number)
  2  return number
  3  is
  4  v1 number := amount;
  5  begin
  6  select (v1*10/100) into v1 from dual;
  7  return (v1);
  8* end;
SQL> /

Function created.
SQL> select tax_fun(1000) from dual;

TAX_FUN(1000)
-------------
          100

SQL>


plz. go through documetation for better option.

Hope this helps.
Mohammad Taj.
Re: Timebound trigger [message #201417 is a reply to message #201413] Sat, 04 November 2006 02:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
@user52: And how exactly is this trigger going to fire at a predefined date/time? You are going to delete a record from table a at that moment?

@op:
Triggers in the Oracle-vocabulary are pieces of code that are fired as a result from an action. Most triggers are defined to react upon an insert, delete or update of the data in a table, but there are some other (less used) triggering-events.
What you seem to be looking for is a JOB. Jobs are 'commands' in a job-queue waiting to be executed at a certain moment in time. Take a look at DBMS_JOB in the documentation
Re: Timebound trigger [message #201419 is a reply to message #201417] Sat, 04 November 2006 02:44 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Right sorry forget to read "date".

Wink
Taj.


Previous Topic: Bulk processing features in what Oracle version?
Next Topic: about triggers
Goto Forum:
  


Current Time: Sun Dec 11 08:30:25 CST 2016

Total time taken to generate the page: 0.16667 seconds