ORA-30506 error trying to create trigger

[gandolf989]

The subquery is treated as a table and app is the name of that table.
Did you try this and does it work? i.e. is it commiting the transaction?

[gandolf989]

Originally posted by sleepyhead
hehe, everything is relative. I know I drink too much coffee, but it's not as bad as it seems; I only drink it for the Irish Whiskey!

SQL> select Name from operator;

NAME
------------
Chris
James
Larry
Jones
Adams
Mark
Nancy
Barbara

Jim can come be an operator, we don't have a Jim yet If my little database seems contrived, that's only because it is. This is an exercise in learning... But your point is a good one. FWIW, the Operator job sucks anyway, Jim is much better off as a PORNSTAR. I won't mention what the PrimaryKey is for that table

If you really want to create an example than use a surrogate key. Add a number(12) column called pkey, create a sequence, populate the pkey column in the table, create an row level before insert trigger to populate the pkey column, make the pkey column the primary key. Add pkey_"table_name" to every table where there is a foreign key, populate pkey based on the operator column drop the old foreign key and add a new one with pkey. Got all that?

Oh and in your "app" when you add an operator use the returning clause to get the pkey value back from the insert statement.

[sleepyhead]

Yup, I think I got all that, in concept. The implementation will take a little more thought, but I'll get there...

In the meantime, I think the reason that the trigger isn't deleting the OPERATOR rows is because there is a FK in the APPOINTMENT refering to OPERATOR with the default "ON DELETE RESTRICT" integrity constraint... It's funny how computers do exactly what you tell them to do... So, I need to delete all appointments for a given operator, then delete the operator. Lemme work on this a bit...

[gandolf989]

Originally posted by sleepyhead
Yup, I think I got all that, in concept. The implementation will take a little more thought, but I'll get there...

In the meantime, I think the reason that the trigger isn't deleting the OPERATOR rows is because there is a FK in the APPOINTMENT refering to OPERATOR with the default "ON DELETE RESTRICT" integrity constraint... It's funny how computers do exactly what you tell them to do... So, I need to delete all appointments for a given operator, then delete the operator. Lemme work on this a bit...

That you need to figure this stuff out should make this a better learning experience.
While you are in learning mode take a look at "PL/SQL Best Practices" by Steven Feuerstein.

[sleepyhead]

Thanks guys, here's the trigger that works:

CREATE TRIGGER PurgeOnStartup AFTER STARTUP ON DATABASE
DECLARE 
	CURSOR OpCursor IS SELECT Name
		FROM OPERATOR,
			(SELECT OperatorName, MAX(appdate) MaxAppDate FROM appointment
			GROUP BY OperatorName) NewestApp
		WHERE OPERATOR.Name = NewestApp.OperatorName
		AND SYSDATE - NewestApp.MaxAppDate > 90;
	OpVal OpCursor%ROWTYPE;	
	
BEGIN
   FOR opval IN opcursor
   LOOP
	   DELETE FROM APPOINTMENT WHERE OperatorName = OpVal.Name;
	   DELETE FROM operator WHERE Name = opval.Name; 
   END LOOP;

END;
/

I also tried it with OPEN, FETCH, and CLOSE on the cursor, but it didn't work. Is the FOR loop the preferred method?

Also, how could I have done this without the xplicit cursor? I'm going to try something like:

DELETE FROM APPOINTMENT WHERE OperatorName IN (Select queries/subqueries)

Is there any advantage to not using the explicit cursor?

Thanks for the help gandolf!

[sleepyhead]

Here it is without the explicit cursor, works great (and a bit easier to understand):

CREATE TRIGGER Purgetest AFTER STARTUP ON DATABASE

BEGIN

DELETE FROM APPOINTMENT WHERE OperatorName IN ( 
	SELECT Name FROM OPERATOR,
		(SELECT OperatorName, MAX(appdate) MaxAppDate FROM appointment
			GROUP BY OperatorName) NewestApp
		WHERE OPERATOR.Name = NewestApp.OperatorName
		AND SYSDATE - NewestApp.MaxAppDate > 90);
	
DELETE FROM OPERATOR WHERE Name NOT IN (
	SELECT OperatorName FROM APPOINTMENT);

END;
/

Which is the "Best" way to do it, with or without explicit cursor? The only difference in the end result that I can see is that the first method (with explicit cursor) does not delete operators that have never had an appointment, while the second method does... Are there any other issues? Performance, security, etc?

[gandolf989]

Originally posted by sleepyhead
Which is the "Best" way to do it, with or without explicit cursor? The only difference in the end result that I can see is that the first method (with explicit cursor) does not delete operators that have never had an appointment, while the second method does... Are there any other issues? Performance, security, etc?

DELETE FROM OPERATOR WHERE Name IN (
   SELECT Name FROM OPERATOR,
        ( SELECT OperatorName, MAX(appdate) MaxAppDate 
            FROM appointment
           GROUP BY OperatorName) NewestApp
           WHERE OPERATOR.Name = NewestApp.OperatorName
             AND SYSDATE - NewestApp.MaxAppDate > 90);

You need the same where clause for both delete statements. The code that I wrote had an implicit cursor. I did not define "OpVal OpCursor%ROWTYPE;" and I did not need to define it, it was implicitly defined, and by using a FOR LOOP the cursor was implicitly opened and closed and all of the rows were implicitly fetched. But doing everything with two explicit delete statements is probably a better method.