triggers recursives

[Guest_imported]

I need information about the triggers in oracle 8, how can we make them not to be recursives, becouse we want a trigger, to update a row when inserted, or updated, but, when you do that , the trigger calls himself again and again, how can we stop that??

 

[Kenrae]

You could try to call a procedure which makes the update, instead of updating de row directly in the trigger's code.
And you should have a condition when the trigger doesn't update a row, I suppose. Do you need to update when the trigger has been called by another trigger?

 

[stevecal]

The other thing you could try is to turn the trigger on and off as necessary.

You would do this with dynamic SQL. There is some code here which should cover most of the bases for you:

FUNCTION Trigger_Status( in_trigger_name IN VARCHAR2) RETURN VARCHAR2
IS
result VARCHAR2(36);
BEGIN
select status INTO result from user_triggers where trigger_name = in_trigger_name;
return result;
END;


Procedure Trigger_Off( trigger_name IN VARCHAR2 )
IS
t_string VARCHAR2(512);
BEGIN

IF Trigger_Status( UPPER(trigger_name))= 'ENABLED' THEN
t_string := 'ALTER TRIGGER ' || trigger_name || ' DISABLE';
do(t_string);
ELSE
null;
END IF;

END;

Procedure Trigger_On( trigger_name IN VARCHAR2 )
IS
t_string VARCHAR2(512);
BEGIN
IF Trigger_Status( UPPER(trigger_name))= 'DISABLED' THEN
t_string := 'ALTER TRIGGER ' || trigger_name || ' ENABLE';
do(t_string);
ELSE
null;
END IF;

END;

-- this code is deprecated in Oracle 8i
-- replaced everything between BEGIN & END
-- with the following:
-- EXECUTE IMMEDIATE action;

Procedure do(action IN VARCHAR2 )
IS
v_curs INTEGER;

BEGIN
v_curs := dbms_sql.open_cursor;

dbms_sql.parse(v_curs, action, dbms_sql.native);
-- NB + FYFI: the parse will also execute the statement

dbms_sql.close_cursor(v_curs);

END;

 

[MikeJones]

I'd be vert wary of switching triggers on and off, while the trigger is off other statements can be coming through which you want to catch. If I remeber correctly the official Oracle recommended approach is to use a combination of Statement level triggers, temporary tables and row level triggers on the temp table. The statement level trigger does not suffer from the recursive problem so you can insert all the rows into the temp table and then from there process them back into your original table. Check out technet for the full answer, as it's oracle's official line I would expect it to be there.

 

[stevecal]

Agreed. But if all your accesses are brokered by sps and the sps check the status of the trigger, you don't have an issue here. Mind you it pretty much makes triggers redundant (which is why we moved to sp-brokered access in the first place...).

 

[MikeJones]

Sp-brokered access?? How does this work?

 

[stevecal]

Only packaged stored procedures have insert/update/delete access to tables i.e. we do not allow any client-side SQL to affect the dbms contents.

This means that we can control whether or not we allow a trigger to fire and in what context since our stored procedures fire with an indication of 'context' i.e. they are passed an argument to indicate whether triggers should be active or inactive (actually we also can turn constraints on or off, fire sub-processes or disallow the processes themselves, etc, etc).

 

[MikeJones]

What happens if you want to update 50,000 rows on the table? do you have to write a pl/sql program to loop and call the SP 50,000 times?

 

[sem]

It's generally not a good idea to use DDL statements for processing DML ones. There are some reasons:

1. Alter trigger implyies commit;
2. Anybody may change data while triggers are disabled (even by other user);
3. Anybody who need to change data by disabling trigger must be either table owner or has alter any trigger priviledge.

Is it not enough?

As for Oracle recomendations, you may use the combination of before statement-level trigger, packaged pl/sql table, populated in row-level trigger and finally after statement-level trigger, processing not :new values, but ones stored in pl/sql table. To avoid recursions set "processing" flag in before-statement trigger, check it in after-row trigger and clear in after-statement.

 

[stevecal]

Mike

With regard to processing of large numbers of rows, the answer is yes and no, I have procedures in place which handle the preponderance of the bulk updates, and we add to these as we discover new permutations of bulk update. There are occasions (which are becoming more and more infrequent - we maybe get one a month now)which require a loop process to be custom written because they are unusual or anomalous i.e. typically they join > 4 tables, or they have weird 'WHERE' clauses. We add these to the unpinned packages, nevertheless, on the grounds that they may get reused or otherwise recycled someday.