Solved

instead of trigger on view

Posted on 2014-01-05
3
564 Views
Last Modified: 2014-01-19
Hi experts,

1) Can I insert data into base table using instead of trigger on view?

2) how I will pass values to instead of trigger on view.

I see that whenever there are more than one tables are involved in view than we can update using instead of trigger on view.

3) will instead of trigger always fire if set on view?

Thanks
meetDinesh
0
Comment
Question by:Dinesh Kumar
  • 2
3 Comments
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 500 total points
Comment Utility
Try to use if you need trigger on  tables:
however there are possible answers to your 3 questions:
1. yes
2. same way as for table (see below post links)
3. depends on the view structure - see below link

check

Using INSTEAD OF Triggers On Views
http://msdn.microsoft.com/en-us/library/def01zh2.aspx

and
INSTEAD OF INSERT Triggers
http://technet.microsoft.com/en-us/library/ms175089(v=sql.105).aspx

some of limitations

Views and Instead of Update Trigger Limitation. Why?
http://social.msdn.microsoft.com/Forums/en-US/87c173b0-8f87-4aa3-b861-d40f23803a43/views-and-instead-of-update-trigger-limitation-why
0
 

Author Comment

by:Dinesh Kumar
Comment Utility
Hi if you can see the following also until I am reading your links.

Why do I  use instead of Trigger on views when I know I can directly fire an insert query using view?

what is the main use of writing sql query in view other than security?

when instead of trigger on view gets executed?
0
 
LVL 42

Accepted Solution

by:
EugeneZ earned 500 total points
Comment Utility
<Why do I  use instead of Trigger on views when I know I can directly fire an insert query using view?>
if your view is "updateable" you can set trigger on such view
to control INSERT/UPDATE potentially multiple tables underneath the view.

According to SQL Server  Books Online (BOL), a view is updateable only if it satisfies the following conditions:
•The SELECT statement defining the view has no aggregate functions in the select list and doesn't contain any TOP, GROUP BY, UNION, or DISTINCT clauses. You can use aggregate functions in a subquery in the FROM clause as long as the statement doesn't modify the values that the functions return. (One exception to the restriction on having UNION in the view definition is when the view is a partitioned view. .)
•The SELECT statement has no derived columns in the select list. (A derived column is anything other than a simple column, such as price * ytd_sales.)
•The FROM clause in the SELECT statement references at least one table.



<what is the main use of writing sql query in view other than security?>
limit user direct  access to sensitive data\columns
<Views can serve as security mechanisms by restricting the data available to users. Some data can be accessible to users for query and modification, while the rest of the table or database is invisible and inaccessible. Permission to access the subset of data in a view must be granted, denied, or revoked, regardless of the set of permissions in force on the underlying table(s).>
more : http://technet.microsoft.com/en-us/library/aa905180(v=sql.80).aspx


<when instead of trigger on view gets executed? >


<INSTEAD OF triggers are executed instead of the triggering action. These triggers are executed after the inserted and deleted tables reflecting the changes to the base table are created, but before any other actions are taken. They are executed before any constraints, so can perform preprocessing that supplements the constraint actions.>

more:
DML Trigger Execution
http://technet.microsoft.com/en-us/library/ms179304(v=sql.105).aspx
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now