• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 613
  • Last Modified:

instead of trigger on view

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?

Dinesh Kumar
Dinesh Kumar
  • 2
2 Solutions
Eugene ZCommented:
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


Using INSTEAD OF Triggers On Views


some of limitations

Views and Instead of Update Trigger Limitation. Why?
Dinesh KumarAuthor Commented:
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?
Eugene ZCommented:
<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.>

DML Trigger Execution
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now