| Author |
Topic |
|
sinva
Starting Member
23 Posts |
 Posted - 2010-05-23 : 22:14:57
|
| Dear All,I have a table A_tbl and a view A_vw on that table. Is there any way that to implement a trigger on the table but not on the view?Thanks all for your suggestions and answers |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-05-23 : 22:18:39
|
yes. you can have triggers on the table.
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
sinva
Starting Member
23 Posts |
Posted - 2010-05-23 : 22:27:17
|
| Thank you KH. But when I create a trigger on the A_tblBut it seems that when I have update on A_vw, it will have checking on view also.Is there any way not to have this checking on the view?? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-05-23 : 22:30:27
|
the view A_vw is based on A_tbl. So whenever you have insert, update or delete on the view, it will update the base table A_tbl and the triggers on table A_tbl will be triggered.What do you want to do actually ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
sinva
Starting Member
23 Posts |
Posted - 2010-05-23 : 22:45:51
|
| as in some case i want to have my trigger to rollback transaction when someone just update the A_tbl, however I would like to allow someone to update the A_tbl when it is joined to other table in the view A_vw |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-05-23 : 23:06:09
|
only GRANT SELECT rights to user for A_tblGRANT SELECT, INSERT, UPDATE to user for view A-vw
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
sinva
Starting Member
23 Posts |
Posted - 2010-05-23 : 23:51:13
|
| Sorry KH,I did not state clearly, as I would like to have just some columns in A_tbl not to allow user to update, is there any way could do this??Thank you for your reply |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-05-24 : 00:53:51
|
In order to do that, you will need to somehow differentiate the updates is directly on the table or via the view. I am afraid there isn't a way to do that on the database level.Maybe a tweak to it is you to use an additional column to control this. - The trigger on A_tbl will roll back changes on those columns if the value of the control column is not some predetermine value. - Use a instead of trigger on the view and the trigger will update the control column with the predetermine value.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
sinva
Starting Member
23 Posts |
Posted - 2010-05-25 : 22:14:26
|
| OK Thank you KH |
|
|
|
|
Trigger on table / view