Scheduled procedures? 1

[biddingbong]

Is it possible to call different procrdures at a specific time (without using a timer)?
Well, I've got a table which contains wat to do on and at which time. With a timer, I check the database each minute whether a procedure sould be called or not. But if I want to check the databace each 2 seconds, the app will use too much resources and the machine becomes slower.
Is there another way of doing it?
Even a non-database way?
Thanks.

 

[chiph]

If you're going to hit the database that often, things will indeed be slower than hitting it once a minute.

A timer is how this is done. Either use the VB GUI control, or write your application as an ordinary .exe and have the windows scheduler (the old at.exe command) run it for you.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[557]

what exactly is the thing you are trying to do? do you want to do the thing thru the program itself?

you could write a trigger in the database to do your job when an event takes place. isn't it more efficient.

 

[biddingbong]

I want my program to do everything. No external scheduler. I didnt understand the database trigger thing.

 

[SBendBuckeye]

A trigger is code that is run by a database (SQL Server, but not Access) when a certain event occurs. Some examples would be when a record is added to a table, deleted from a table, etc.

Have a great day!

j2consulting@yahoo.com

 

[557]

trigger is a procedure that automatically executes when an event we've mentioned in the trigger occurs. so, if you use a trigger, you needn't keep on checking whether the event has occured, (that is, if the event you desire is something concerning the tables in the database. )


i'm pasting below some basics about triggers

Basic Trigger Syntax
Below is the syntax for creating a trigger in Oracle (which differs slightly from standard SQL syntax):
CREATE [OR REPLACE] TRIGGER <trigger_name>

{BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON <table_name>

[REFERENCING [NEW AS <new_row_name>] [OLD AS <old_row_name>]]

[FOR EACH ROW [WHEN (<trigger_condition>)]]

<trigger_body>
Some important points to note:
You can create only BEFORE and AFTER triggers for tables. (INSTEAD OF triggers are only available for views; typically they are used to implement view updates.)
You may specify up to three triggering events using the keyword OR. Furthermore, UPDATE can be optionally followed by the keyword OF and a list of attribute(s) in <table_name>. If present, the OF clause defines the event to be only an update of the attribute(s) listed after OF. Here are some examples:
... INSERT ON R ...

... INSERT OR DELETE OR UPDATE ON R ...

... UPDATE OF A, B OR INSERT ON R ...
If FOR EACH ROW option is specified, the trigger is row-level; otherwise, the trigger is statement-level.
Only for row-level triggers:
The special variables NEW and OLD are available to refer to new and old tuples respectively. Note: In the trigger body, NEW and OLD must be preceded by a colon (":"), but in the WHEN clause, they do not have a preceding colon! See example below.
The REFERENCING clause can be used to assign aliases to the variables NEW and OLD.
A trigger restriction can be specified in the WHEN clause, enclosed by parentheses. The trigger restriction is a SQL condition that must be satisfied in order for Oracle to fire the trigger. This condition cannot contain subqueries. Without the WHEN clause, the trigger is fired for each row.
<trigger_body> is a PL/SQL block, rather than sequence of SQL statements. Oracle has placed certain restrictions on what you can do in <trigger_body>, in order to avoid situations where one trigger performs an action that triggers a second trigger, which then triggers a third, and so on, which could potentially create an infinite loop. The restrictions on <trigger_body> include:
You cannot modify the same relation whose modification is the event triggering the trigger.
You cannot modify a relation connected to the triggering relation by another constraint such as a foreign-key constraint.

--------------------------------------------------------------------------------
Trigger Example
We illustrate Oracle's syntax for creating a trigger through an example based on the following two tables:
CREATE TABLE T4 (a INTEGER, b CHAR(10));

CREATE TABLE T5 (c CHAR(10), d INTEGER);
We create a trigger that may insert a tuple into T5 when a tuple is inserted into T4. Specifically, the trigger checks whether the new tuple has a first component 10 or less, and if so inserts the reverse tuple into T5:
CREATE TRIGGER trig1
AFTER INSERT ON T4
REFERENCING NEW AS newRow
FOR EACH ROW
WHEN (newRow.a <= 10)
BEGIN
INSERT INTO T5 VALUESnewRow.b, :newRow.a);
END trig1;
.
run;
Notice that we end the CREATE TRIGGER statement with a dot and run, as for all PL/SQL statements in general. Running the CREATE TRIGGER statement only creates the trigger; it does not execute the trigger. Only a triggering event, such as an insertion into T4 in this example, causes the trigger to execute.

 

[biddingbong]

Great, have to try this. N here's a sheriff sign.

 

[557]

thanks