Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating Trigger on View Expand / Collapse
Author
Message
Posted Saturday, January 5, 2013 2:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, November 8, 2014 4:17 AM
Points: 206, Visits: 509
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
Post #1403208
Posted Saturday, January 5, 2013 4:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:06 AM
Points: 225, Visits: 1,753
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
Post #1403211
Posted Sunday, January 6, 2013 7:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #1403331
Posted Sunday, January 6, 2013 11:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:12 AM
Points: 7,135, Visits: 12,746
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
Post #1403346
Posted Sunday, January 6, 2013 10:58 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #1403407
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse