?
Solved

instead of trigger on view

Posted on 2014-01-05
3
Medium Priority
?
605 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 2000 total points
ID: 39757476
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
ID: 39757751
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 43

Accepted Solution

by:
Eugene Z earned 2000 total points
ID: 39758044
<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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

649 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