Trigger to a View

Question

Trigger to a View

ionas

Mr or Mrs. 500

Points: 551
More actions
August 31, 2010 at 2:22 am

#226342

SQL server 2005 has an Oracle linked server. A View in SQL 2005 (VCustOrders) is derived from an Oracle Table (TCustOrders).
What I need is to fire a trigger in SQL 2005 when a new order is inserted to Oracle table (TCustOrders). To my knowlwdge firing triggers is not availbale from Oracle to SQL. How can I use the View in SQL (VCustOrders) to fire a trigger ? The trigger should insert Order row to SQL table (TblCustOrders) when a new Order is inserted in Oracle table (TCustOrders).
I tried below trigger but its not fired:
ALTER TRIGGER [dbo].[TrNewOrderHeader]
ON [dbo].[VCustOrders]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.TblCustOrders
SELECT * FROM Inserted
END
ANY IDEAS PLEASE ?????

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

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

If you need to fire a trigger when the row is inserted in Oracle, then you need to create the trigger in Oracle, not SQL Server. You can create a database link from Oracle to SQL Server.

However, if the Oracle database is a critical system, you need to be very careful about how you create the trigger and what will happen if the SQL Server is unavailable.

It might be better to look at Oracle Streams for this kind of Change Data Capture or poll for changes on a schedule if it doesn't need to be real-time...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

That trigger will only fire when a row is inserted into the view in SQL. A SQL trigger cannot be fired by a change in Oracle. To catch changes made in Oracle, you'll need to do something (trigger or other auditing method) in Oracle.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ionas Mr or Mrs. 500 Points: 551 More actions · Answer 3

Hi,

Oracle comes with SAP so its a critical system...! We already have a job that we scheduled to run every 5 minutes. The job covers completely what we need to have with a trigger BUT the users need "more" on line system. Thats why we are investigating triggers functionalities.

The problem is, its not allowed to write any code to Oracle. What we can do is to play with SQL and ONLY SQL.

ionas Mr or Mrs. 500 Points: 551 More actions · Answer 4

Hi Gail,

ok the trigger is fired when a new row is inserted to SQL View. Since the View is bounded to Oracle Table, I thought when a new row is inserted to Oracle Table a new row is inserted to SQL View also. I am right ? Any other suggestion ?

HowardW One Orange Chip Points: 29109 More actions · Answer 5

No. There are no solutions that can be implemented entirely within SQL Server that will be able to capture the event of a row getting inserted into the Oracle database in real time.

If you cannot make changes to the Oracle system, all you can do is poll for changes on as short an interval as possible.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

ionas (8/31/2010)
Since the View is bounded to Oracle Table, I thought when a new row is inserted to Oracle Table a new row is inserted to SQL View also. I am right ?

No. When rows are inserted into the Oracle table, they are not inserted into your view. A view is just a saved select statement, it doesn't store anything. If new rows are added in Oracle, the next time the view is queried those rows will be returned. Prior to that, SQL has no idea they exist.

An insert trigger on a view is fired when you run an insert statement against the view, not against the base tables that the view depends on. That's true regardless of whether the base tables are SQL or Oracle or something else.

Any other suggestion ?

As Howard and I said, if you want to catch an insert in Oracle, you need to write code in Oracle to catch the insert. Otherwise a scheduled job in SQL that pulls the entire table regardless of changes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 7

you might be able to add a job which checks for new entries in the view on an hourly basis or something, and have that job insert the new data;

but with the oracle server being a linked server in a view, performance might be horrible; you need to keep track of the last ID entered or look for a timestamp to keep the number of records to a minimum.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!