3

I've read the Oracle docs on creating triggers and am doing things exactly how it shows, however this just isn't working. My goal is to update the TPM_PROJECT table with the minimum STARTDATE appearing in the TPM_TRAININGPLAN table. Thus, every time someone updates the STARTDATE column in TPM_TRAININGPLAN, I want to update teh TPM_PROJECT table. Here's what I'm trying:

CREATE TRIGGER Trigger_UpdateTrainingDelivery
    AFTER DELETE OR INSERT OR UPDATE OF STARTDATE
    ON TPM_TRAININGPLAN
    FOR EACH ROW WHEN (new.TRAININGPLANTYPE='prescribed')
    BEGIN
       UPDATE TPM_PROJECT SET TRAININGDELIVERYSTART = (SELECT MIN(TP.STARTDATE) FROM TPM_TRAININGPLAN TP WHERE TP.PROJECTID = new.PROJECTID AND TP.TRAININGPLANTYPE='prescribed')
       WHERE PROJECTID = new.PROJECTID
    END;

The trigger is created with no errors, but I do get a warning:

 Warnings: ---> 
   W (1): Warning: execution completed with warning
          <---

Of course Oracle isn't nice enough to actually tell me what the warning is, I simply am shown that there is one.

Next, if I update the training plan table with:

UPDATE TPM_TRAININGPLAN
set STARTDATE = to_date('03/12/2009','mm/dd/yyyy')
where TRAININGPLANID=15916;

I get the error message:

>[Error] Script lines: 20-22 ------------------------
 ORA-04098: trigger 'TPMDBO.TRIGGER_UPDATETRAININGDELIVERY' is invalid and failed re-validation
 Script line 20, statement line 1, column 7

Any ideas what I'm doing wrong? Thanks!

Improve this question
1
  • To show the warning message enter show errors. Also, it appears you are missing the bind colons on :new in your statement between BEGIN and END. Commented Sep 21, 2011 at 23:12

2 Answers 2

Reset to default
4

A few issues in no particular order.

First, in the body of a row-level trigger, you need to use :new and :old to reference the new and old records. The leading colon is necessary. So your WHERE clause would need to be

WHERE PROJECTID = :new.PROJECTID

Second, if you are running your CREATE TRIGGER in SQL*Plus, you can get a list of the errors and warnings using the SHOW ERRORS command, i.e.

SQL> show errors

You could also query the DBA_ERRORS table (or ALL_ERRORS or USER_ERRORS depending on your privilege level) but that's not something you normally need to resort to.

Third, assuming the syntax errors get corrected, you're going to get a mutating table error if you use this logic. A row level trigger on table A (TPM_TRAININGPLAN in this case) cannot query table A because the table may be in an inconsistent state. You can work around that, as Tim shows in his article, by creating a package with a collection, initializing that collection in a before statement trigger, populating the data in the collection in a row-level trigger, and then processing the modified rows in an after statement trigger. That's a decent amount of complexity to add to the system, however, since you'll have to manage multiple different objects.

Generally, you'd be better off implementing this logic as part of whatever API you use to manipulate the TPM_TRAININGPLAN table. If that is a stored procedure, it makes much more sense to put the logic to update TPM_PROJECT in that stored procedure rather than putting it in a trigger. It is notoriously painful to try to debug an application that has a lot of logic embedded in triggers because that makes it very difficult for developers to follow exactly what operations are being performed. Alternately, you could remove the TRAININGDELIVERYSTART column from TPM_PROJECT table and just compute the minimum start date at runtime.

Fourth, if your trigger fires on inserts, updates, and deletes, you can't simply reference :new values. :new is valid for inserts and updates but it is going to be NULL if you're doing a delete. :old is valid for deletes and updates but is going to be NULL if you're doing an insert. That means that you probably need to have logic along the lines of (referencing Tim's package solution)

BEGIN
  IF inserting 
  THEN
    trigger_api.tab1_row_change(p_id => :new.projectid, p_action => 'INSERT');
  ELSIF updating
  THEN
    trigger_api.tab1_row_change(p_id => :new.projectid, p_action => 'UPDATE');
  ELSIF deleting
  THEN
    trigger_api.tab1_row_change(p_id => :old.projectid, p_action => 'DELETE');
  END IF;
END;
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

So sounds like what I'm trying to do is simply not doable. Right now, we compute the value every time and it's very, very slow. I'm attempting to find ways to improve the performance of these queries. Updating it in the middle-tier is possible, but will not be a small task. However, even fixing all the syntax errors I still cannot create the trigger. For some reason, SQLPlus doesn't work for me - It just wants more and more input, like something is not being closed or ended though I can't figure out what it is.
try entering a "/" on the last line.
I left for today, but I'll give that a shot tomorrow.. I think the trigger thing is a no-go for the points that Justin mentioned, especially the mutating table error even if I do get everything working.. It sounds like the best approach is to update the value from the middle tier. All the SQL code is written on the .NET Entity Framework and it's a huge mess right now, but hopefully I can find some "hook" to update this column every time the table changes..
@Mike - How are you computing the value every time? What is the query plan? It shouldn't be "very, very slow" if there is an index on (PROJECTID, STARTDATE) in the TPM_TRAININGPLAN table unless you have a whole lot of rows with the same PROJECTID in the TPM_TRAININGPLAN table.
It's very, very slow because we're selecting thousands of rows from PROJECTVERSIONS, and each of these rows have dozens of TRAININGPLAN rows associated with them. So for each of those rows, it has to compute the lowest date (a sequential scan is the only way to do that). I'd like to normalize this data by just storing the lowest date in the PROJECT table directly, and updated it any time a TRAININGPLAN row changes.
|
2

As Justin Cave have suggested, you can calculate the minimum start date when you need it. It might help if you create an index on (projectid, startdate);

If you really have a lot of projects and training plans, another solution could be to create a MATERIALIZED VIEW that has all the data that you need:

CREATE MATERIALIZED VIEW my_view
... add refresh options here ...
AS
SELECT t.projectid,  MIN(t.start_date) AS min_start_date
FROM TPM_TRAININGPLAN t
GROUP BY t.projectid;

(sorry, don't have Oracle running, the above code is just for the reference)

Improve this answer

3 Comments

Unfortunately, Oracle doesn't support materialized views with nested sub-queries. Otherwise, I would do this for sure..
Join your simple materialized view with the rest of your query, no need to put everything into the view, it would still be plenty fast.
The materialized view you created above would need to be manually updated and would not support FAST REFRESH. These are deal breakers for me. I need data in real time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.