How to create a PL/SQL trigger to update a value of column in another table

Hi all,

I have two tables in my Oracle database

I have to create a trigger so that the 'count' column value of respected 'batch_code' in 'batches' table should be increased by 1 whenever there is an insertion in students table.

Please help me how this can be done

Whilst this is easy enough to do, we would question whether this is a good idea. From a requirements perspective it seems that you want to maintain the count for each batch in line with the number of students in a particular batch and you gave decided on using a trigger to do this.

You have said that Oracle should fire this trigger whenever a row is inserted into the students table. However you have said nothing about what happens when a row is deleted from the students table or anything about what should happen when a student's batch code is changed. Is this allowed? We don't know because you haven't said.

That therefore is 1 good reason not to use a trigger for this. Yes, you could always create 2 more PL/SQL triggers on the Students table - one for updates and one for deletes but you're making life difficult for yourself if you do that.

If that's not enough, there is also a 2nd and much better reason why you shouldn't do it this way. That is you're hiding application logic. If this is a requirement of the application then it should be included with the code that creates Students, otherwise you increase the costs of maintenance. Even if it's well documented you still have to hunt for the rest of the application logic in the trigger.

We still haven't finished however. Triggers can be dropped or disabled which means that Oracle won't fire them which means that you're application will still run but your data will be inconsistent.

Having said all that, if you're still determined to use a PL/SQL trigger the code would be something like the following:

create or replace
trigger t3 after insert on new_students for each row
   update batches set count = count + 1
   where batch_code = :new.batch_code;

To learn more about PL/SQL we recommend our series of tutorials.

We also recommend reviewing the following questions and answers about PL/SQL and triggers:

The quickest way to learn though is by taking a training course. With our partners we offer classroom-based training in the UK and the US and on=demand, on-line training. See here for more details.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.