Solved

instead of trigger on view

Posted on 2014-01-05
3
565 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
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 42

Accepted Solution

by:
EugeneZ earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

919 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

14 Experts available now in Live!

Get 1:1 Help Now