DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: ORA-30506 error trying to create trigger

  1. #1
    Join Date
    Mar 2005
    Posts
    9

    ORA-30506 error trying to create trigger

    I'm running Oracle 9i, and trying to create a database-level trigger after startup. Database name is DKDB. Here is the beginning of the trigger code:

    CREATE TRIGGER PurgeOnStartup
    AFTER STARTUP ON DKDB

    And here is the error:

    AFTER STARTUP ON DKDB
    *
    ERROR at line 2:
    ORA-30506: system triggers cannot be based on tables or views

    I have not created any tables or views named DKDB. I'm logged in with DBA privs. I've already logged in as sys and ran dbmsstdx.sql, What am I doing wrong?

    Thanks!

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    It should be:

    CREATE TRIGGER PurgeOnStartup
    AFTER STARTUP ON DATABASE
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Mar 2005
    Posts
    9
    DOH! I feel a bit silly. That worked great, thanks. Now I get to figure out what compilation errors I've got...

  4. #4
    Join Date
    Mar 2005
    Posts
    9
    OK, I've fixed all compilation errors, but the trigger doesn't do anything when I shutdown and start the database using Oracle Enterprise Manager. This trigger involves 2 tables: OPERATOR and APPOINTMENT. APPOINTMENT has a foreign key (OperatorName) that refers to OPERATOR.Name (primary key of OPERATOR). The trigger is suppossed to remove all OPERATORs that have not scheduled an APPOINTMENT in the last 90 days. I'm trying to loop through a cursor, count the number of appointments for each operator in the last 90 days, and delete the corresponding OPERATOR row if there are none. What am I missing? Here is the trigger code:

    CREATE TRIGGER PurgeOnStartup AFTER STARTUP ON DATABASE

    DECLARE
    CURSOR OpCursor IS SELECT * FROM OPERATOR;
    OpVal OpCursor%ROWTYPE;
    AppCount INTEGER;

    BEGIN
    OPEN OpCursor;
    LOOP
    FETCH OpCursor INTO OpVal;
    EXIT WHEN OpCursor%NOTFOUND;

    SELECT COUNT(*) INTO AppCount
    FROM APPOINTMENT
    WHERE OperatorName = OpVal.Name
    AND SYSDATE - AppDate < 90;

    IF AppCount = 0
    THEN DELETE FROM OPERATOR WHERE Name = OpVal.Name;
    END IF;

    END LOOP;
    CLOSE OpCursor;
    end;
    /

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Avoid using SELECT COUNT(*), aviod using explicit cursors, and make sure that you do a commit. This should work, although I might have transposed some of the columns names. You might have to do this as an autonomous Transaction in order to get it to commit.

    Code:
    CREATE TRIGGER PurgeOnStartup 
     AFTER STARTUP ON DATABASE
    DECLARE
    	CURSOR opcursor IS 
          SELECT operator.operatorname 
            FROM operator, appointment 
           WHERE operator.operatorname = appointment.name AND 
                 SYSDATE - appointment.appdate < 90;	
    BEGIN
       FOR opval IN opcursor
       LOOP
    	   DELETE FROM operator WHERE Name = opval.operatorname; 
       END LOOP;
    END;
    /
    Last edited by gandolf989; 03-24-2005 at 12:19 PM.

  6. #6
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Primary key on name? Poor old Jim Smith... wants a job as an operator, only to find out he can't have one because theres already another Jim Smith there!! Fancy that.

    Anyway, nothing to do with the problem so i'll be quiet... do what gandolf989 says...
    Assistance is Futile...

  7. #7
    Join Date
    Mar 2005
    Posts
    9
    Gandolf,

    Thanks, I didn't know you could use a WHERE clause in the cursor declaration. However, if I'm following your logic correctly, your trigger will delete any operator that has had an appointment in the last 90 days. At first, I thought we could just change the
    Code:
    SYSDATE - appointment.appdate < 90;
    to be Greater Than 90, but that would end up deleting operators with appointment over 90 days old even if they have recent appointments...

    I have some more questions about your advice that I'm going to google...

    Do I need to add a commit statement in the trigger code?

    WHat's an explicit cursor, and why are they bad?

    What's an autonomous transaction?

    What is bad about using SELECT COUNT(*) in a trigger?

    Thanks for the help!

  8. #8
    Join Date
    Mar 2005
    Posts
    9
    Originally posted by waitecj
    Primary key on name? Poor old Jim Smith... wants a job as an operator, only to find out he can't have one because theres already another Jim Smith there!! Fancy that.
    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

  9. #9
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    This should work better. But as waitecj pointed out, are you really using name for the primary key? You may want to make this an procedure with an autonoumous transaction, and then just call the procedure from the trigger.

    Code:
    CREATE TRIGGER PurgeOnStartup 
     AFTER STARTUP ON DATABASE
    DECLARE
    	CURSOR opcursor IS 
          SELECT operator.operatorname 
            FROM operator, 
               ( SELECT name, MAX(appdate) appdate
                   FROM appointment
                  GROUP BY name ) app
           WHERE operator.operatorname = appointment.name AND 
                 SYSDATE - appointment.appdate > 90;	
    BEGIN
       FOR opval IN opcursor
       LOOP
    	   DELETE FROM operator WHERE Name = opval.operatorname; 
       END LOOP;
    END;
    /

  10. #10
    Join Date
    Mar 2005
    Posts
    9
    Thanks again Gandolf! I didn't know you could use a the results of a SELECT statement in a FROM clause. You rock I think I understand the logic in your code, but one thing confuses me... In the cursor declaration:
    Code:
    ( SELECT name, MAX(appdate) appdate
                   FROM appointment
                  GROUP BY name ) app
    What is the last "app" for?

    EDIT: OK, I think I get it... it's the handle for the results of the SELECT statement, right? sort of like:
    Code:
    (SELECT ...) AS app
    Last edited by sleepyhead; 03-24-2005 at 12:42 PM.

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