January 5, 2013 at 2:51 am
Dear,
I have a view that is comprised of multiple tables containing only today's data. I used only select statement in that view. Now I want to write a trigger on that view that when a new row is inserted, it will insert a row into another table.
What can I do for that? Please suggest me in your earlier time.
Regards,
Akbar
January 5, 2013 at 4:19 am
What exactly is your problem? According to BOL "A view can be referenced only by an INSTEAD OF trigger." http://msdn.microsoft.com/en-us/library/ms189799%28v=sql.90%29.aspx
January 6, 2013 at 7:26 am
certainly , we can have trigger (instead of) but i will suggest to set the trigger on table.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 6, 2013 at 11:53 am
Bhuvnesh (1/6/2013)
certainly , we can have trigger (instead of) but i will suggest to set the trigger on table.
That likely will not help the situation. There is unique value added to the system when an instead trigger is placed on a view that joins multiple tables. Namely that we can support inserts into that view where before, without the instead trigger, we could not.
From http://msdn.microsoft.com/en-us/library/ms175521(v=sql.105).aspx
The primary advantage of INSTEAD OF triggers is that they enable views that would not be updatable to support updates. A view based on multiple base tables must use an INSTEAD OF trigger to support inserts, updates, and deletes that reference data in more than one table.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 6, 2013 at 10:58 pm
opc.three (1/6/2013)
Bhuvnesh (1/6/2013)
certainly , we can have trigger (instead of) but i will suggest to set the trigger on table.That likely will not help the situation. There is unique value added to the system when an instead trigger is placed on a view that joins multiple tables. Namely that we can support inserts into that view where before, without the instead trigger, we could not.
From http://msdn.microsoft.com/en-us/library/ms175521(v=sql.105).aspx
The primary advantage of INSTEAD OF triggers is that they enable views that would not be updatable to support updates. A view based on multiple base tables must use an INSTEAD OF trigger to support inserts, updates, and deletes that reference data in more than one table.
diamond studded link ..thanks 🙂
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply