• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

Doubt regarding update trigger

Hi Experts...
I have a main database having all the data and a sub database into which i transfer the content from the main  database to the sub database through an update trigger.

Here the update trigger should work or is of essence  only when updation is done to only two rows or tuples in a table which consistes of thousands of rows or tuples.Or my point of intrest is only two primary keys.

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.

I have not implemented this concept i just wanted to know if this is a feasable idea or any other method which is more efficient and more suitable to this scenario exists.

Please help...
0
gaugeta
Asked:
gaugeta
  • 14
  • 10
1 Solution
 
OP_ZaharinCommented:
"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.
0
 
gaugetaAuthor Commented:
@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...
0
 
gaugetaAuthor Commented:
@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...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
OP_ZaharinCommented:
"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.
0
 
gaugetaAuthor Commented:
@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
0
 
OP_ZaharinCommented:
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;
0
 
gaugetaAuthor Commented:
@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...
0
 
OP_ZaharinCommented:
- 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.
0
 
gaugetaAuthor Commented:
@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...
0
 
gaugetaAuthor Commented:
@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.

0
 
OP_ZaharinCommented:
- 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
0
 
gaugetaAuthor Commented:
@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...
0
 
OP_ZaharinCommented:
gaugeta,
- actually you havent really answered 'whats the used of student2 table'? why didnt you query directly to student1?

- try the following and see if the v_student2 view change as your master table being updated:

create view v_student2 as select id, name, station, address from student1;
/
select * from v_student2;
/
update student1 set address = 'street 33x' where id = 1;
/
select * from v_student2 where id = 1;


- i would suggest you to look into oracle documentation on view and materialized view and see which approach might be suitable for you:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8004.htm
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6002.htm
0
 
gaugetaAuthor Commented:
@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...
0
 
OP_ZaharinCommented:
- then the view should work best for you. have you tried my sample above?
0
 
gaugetaAuthor Commented:
@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.
0
 
gaugetaAuthor Commented:
@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...
0
 
OP_ZaharinCommented:
- 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
0
 
gaugetaAuthor Commented:
@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...
0
 
OP_ZaharinCommented:
- 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.
0
 
gaugetaAuthor Commented:
@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
0
 
OP_ZaharinCommented:
- yes to both of your question. you should read the documentation i've provided in the link earlier to understand what is a View.
0
 
gaugetaAuthor Commented:
@OP_Zaharin:Thanks for the reply.
Will try this and get back if i face any issues.
0
 
leonstrykerCommented:
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.
0
 
gaugetaAuthor Commented:
I am accepting the most suitable solution
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 14
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now