-
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!
-
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?
-
DOH! I feel a bit silly. That worked great, thanks. Now I get to figure out what compilation errors I've got...
-
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;
/
-
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.
-
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...
-
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!
-
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
-
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;
/
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|