ORA-30506 error trying to create trigger - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: ORA-30506 error trying to create trigger

  1. #11
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    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?
    this space intentionally left blank

  2. #12
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    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!
    Code:
    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.
    this space intentionally left blank

  3. #13
    Join Date
    Mar 2005
    Posts
    9
    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...

  4. #14
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    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.
    this space intentionally left blank

  5. #15
    Join Date
    Mar 2005
    Posts
    9
    Thanks guys, here's the trigger that works:
    Code:
    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:
    Code:
    DELETE FROM APPOINTMENT WHERE OperatorName IN (Select queries/subqueries)
    Is there any advantage to not using the explicit cursor?

    Thanks for the help gandolf!

  6. #16
    Join Date
    Mar 2005
    Posts
    9
    Here it is without the explicit cursor, works great (and a bit easier to understand):
    Code:
    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?

  7. #17
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    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?
    Code:
    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.
    Last edited by gandolf989; 03-24-2005 at 03:14 PM.
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width