Doubt regarding update trigger

"The thing i wanted to know is whether my update trigger is consuming resources and time as it needs to check on every updation of each and every row in the database."

- this would depend on how you code your trigger. an update trigger would only execute when update operation is done on a data in the table and if any condition sets is met. trigger is usually used when there is a changes in a data (DML operation), and you want to do further operation based on that data changes. when only 2 rows changed not necessarily that your trigger need to check for the whole rows in that table, this will definitely resource consuming.

- you can also setup a test environment by creating a test table and try your trigger code to see the resource taken and impact on your database performance before implement in on production table/database.

@OP_Zaharin:Thanks for the reply.
So by this can i conclude that my trigger would work only when the update happens only on that two rows and not on the whole data of that particular table and no extra resources are consumed.
If so my issue is almost solved.
Please help...

@OP_Zaharin:Another point as stated by you above "an update trigger would only execute when update operation is done on a data in the table and if any condition sets is met" so it would  check on every data being updated in the table  which is different from the two rows which are the point of focus here to establish the fact whether the data being updated is relevent or not.

Dosent this eat up resources and time which would hamper some other functionality.
Please help...

"So by this can i conclude that my trigger would work only when the update happens only on that two rows and not on the whole data of that particular table and no extra resources are consumed.
If so my issue is almost solved."

- yes. and do implement this on a test table/test environment first not directly to your production table. once its proven to work as you desire then implement it on production table.

"Dosent this eat up resources and time which would hamper some other functionality."
- as i mention, this depends on how you code your trigger. you should code it to reflect only the data that is being updated.

@OP_Zaharin:Thanks for the reply.
I didnt quite get this statement " you should code it to reflect only the data that is being updated."

So my sample main db has the foll 4 rows of data and the first two rows are of essence how do i code the trigger in this scenario:I have 2 tables as shown below and only if address column data is updated on the id's 1 and 2 only then i need to update the corresponding entry in student 2 table.

How do i achieeve this by keeping your statement " you should code it to reflect only the data that is being updated." in mind.
Please help...
db-sample.png

hi gaugeta,
- sorry that i've got confuse with myself - my mistake.
- with your data sample then its much clearer on your requirement. you are right that the trigger will go to each rows (which the trigger code might look as below) therefore it will consume resource depends on the number of rows you have. i would suggest that you handle the update to the second table in your application side if it involves million of rows.

CREATE OR REPLACE TRIGGER update_student
BEFORE UPDATE ON student1
FOR EACH ROW
BEGIN
  UPDATE student2 SET student2.station = :new.station
  WHERE student2.id = :old.id;
END;

@OP_Zaharin:Thanks for the reply.
But the problem is i've got to handle this in database side and cannot use application side as the main database is not mine and i have access to the second table in the sample i.e Student 2 and finding a solution in database is the only option i have got.
Can you suggest an alternative for the same.
Please help...

- can i know what student2 table is for? can you create a materialized view / view instead of having student2 as a table?
- alternatively you can also schedule an update job to run periodically / at the end of the day to 'sync' the data in student1 to student2 table.

@OP_Zaharin:Thanks for the reply.
The student table is the table that powers my application where only specific data is required from the master database.

How would creating  materialized view / view help me to overcome the problem of consuming time and resources.

I dont know whether i'll be allowed to schedule a job to do the same as i am told that i have to find a solution in database side itself.

I can't schedule a job once a day as the data which is of essence will be changing many times in an hour.So i am forced to find a solution in database field itself.
Please help...

@OP_Zaharin:I meant the Student2 table is the table that powers my application where only specific data is required from the master database which is Student1.

- do you only need to query to the student2 table without having to update that table? if yes then it is better that you create a view on student1.

create view v_student2 as select id, name, station, address from student1
/
select * from v_student2


- there is also another alternative to a trigger by using pl/sql api however i'm not familiar with it and had never implement such approach. this is mention is the following link:
http://rwijk.blogspot.com/2007/09/database-triggers-are-evil.html

@OP_Zaharin:Thanks for the reply.

How would creating a view solve this problem.

And would my view be updated as the master table's data is updated i.e only when 2 rows are updated in master table.

And can i create my view as follows:
 create view v_student2 as select id, name, station, address from student1 where Student1.id=1 or Student1.id=2
/
select * from v_student2

Can you please explain how the same effect of triggers is achieved but without its disadvantages by using the view concept.
Please help...

@OP_Zaharin:Thanks for the reply.
The Student1 is the master table and which is containing data regarding all id's.
I have only a handful of id's which are mine and i am allowd access to only those id's though not directly from the master table itself.

Thats the reason i was told to create a new table get the data relevent to me in the new table and access them.

Please suggest any soln for the same.
Please help...

- then the view should work best for you. have you tried my sample above?

@OP_Zaharin:Thanks for the reply.
Currently i am not at the system with the database.I will defenitely try this and get back to you with the results.

@OP_Zaharin:I have a doubt.
The initial proposal was i create a new table i.e in this case the Student2 table get my relevent data into the table newly created and that table alone be shared by creating a new user i.e myself and assigning the ownership of that table to me.

How do i have the same effect with a view.
Please help...

- 1st: grant the student1 schema owner on CREATE VIEW privileged. you need to do this as SYS or SYSTEM user
- 2nd: login as the student1 schema owner and create v_student2 view
- 3rd: GRANT SELECT on the v_student2 view to the new user

@OP_Zaharin:Thanks for the reply.
I did'nt get this statement "grant the student1 schema owner on CREATE VIEW privileged".

Actually i need to convince the dba to do all these operations and grant me the access.
Can you simplify the explanation.
Please help...

- for student1 schema owner/user to create a v_student2 view, the user need to be granted CREATE VIEW privileged. your dba would understand this when you mention the steps to them.

@OP_Zaharin:So after following all the steps i would be able to access the view for any length of time is it.
Is the view going to be there in the database just like a table is what i'm asking.
Thanks for the reply

- yes to both of your question. you should read the documentation i've provided in the link earlier to understand what is a View.

@OP_Zaharin:Thanks for the reply.
Will try this and get back if i face any issues.

I've requested that this question be closed as follows:

Accepted answer: 500 points for OP_Zaharin's comment http:/Q_27384843.html#36931114

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

I am accepting the most suitable solution