create trigger on view

Question

create trigger on view

adisql

SSCrazy

Points: 2893
More actions
August 21, 2020 at 9:05 pm

#3782406

Hi,
is it possible to create Trigger on View ?
Version:Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (Intel X86) Mar 19 2015 13:34:46 Copyright (c) Microsoft Corporation Express Edition with Advanced Services on Windows NT 6.1 <X64> (Build 7601: ) (WOW64)

Thank.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

Yes, you can create an INSTEAD OF trigger on a view. That is, a trigger that fires if an INSERT statement (or which action the view is defined) attempted on the view, whereas the INSERT on the view is not executed.

The normal use for this is to permit operations on a view where a direct INSERT is blocked by SQL Server because it cannot figure out what you mean. For instance, say that the view is a join on two tables. An INSTEAD OF trigger could insert data into both tables.

What is the actual use case you have in mind?

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

adisql SSCrazy Points: 2893 More actions · Answer 2

Basically anytime when a new record is inserted into the specific table\column , it grab the record and bring it over in some way for use in another system.

we have some agreement restrictions not to create Trigger on that Table. so we are trying to create Trigger on View.

adisql SSCrazy Points: 2893 More actions · Answer 3

FYI..

here is my view.only on one column from one table.

USE [Database1]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

CREATE VIEW [dbo].[View_Table1]

AS

SELECT distinct(idnumber)

FROM Table1

GO

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 4

adisql wrote:

Basically anytime when a new record is inserted into the specific table\column , it grab the record and bring it over in some way for use in another system.
we have some agreement restrictions not to create Trigger on that Table. so we are trying to create Trigger on View.

The trigger will only fire if the INSERT statement is against the view. Insertions against the table will not fire the trigger the view. And obviously, if there some policy that precludes adding a trigger on the table, that policy would apply to the view as well.

Maybe you should work with an asynchronous solution that looks for new rows in the tables and copy them to the other system? A trigger is really only needed if the update on the other system must be transactional. That is, if the update on the remote system fails, the update on the target system should fail as well.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

adisql SSCrazy Points: 2893 More actions · Answer 5

Actual my requirement is , if new record insert in sql table then it will be copy over to oracle databse table.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 6

To be able to help you further we need more information. Here is a question that you need to find the answer to: Say that is is not possible to insert the row in the Oracle table. This could be because the row violates a constraint on the Oracle side, because there is a deadlock, or simply because Oracle database or the connection is down. What should happen in this case?

The inserted row in the SQL Server database MUST be rolled back. The systems must be in perfect sync.
The inserted row in the SQL Server database MUST NOT be rolled back.
Something else?

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]