Solved

instead of trigger on view

Posted on 2014-01-05
3
580 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:Eugene Z
Eugene Z 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:
Eugene Z 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
unable to reinstall SCVMM 2012 R2 - SQL errors. 5 35
Conditions in Where clause 9 46
Syntax issue with my Where Clause SQL 2012 20 38
T-SQL: Stored Procedure Syntax 3 29
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

713 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