Trigger on a view from a linked server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger on a view from a linked server

Author

Topic

chris_lunt
Starting Member

25 Posts

Posted - 2013-02-18 : 06:59:16

Hello Folks

I've created a view that selects data from a linked server, then created a trigger on that view to insert into another table. I've used both instead of insert and instead of update bt nothing seems to be happening.

Can triggers be cvreated on vioews that look at linked servers?

Here's my code for the trigger (made a simple insert just to check it asn;t being fired).

ALTER TRIGGER [dbo].[TriggerPharmacyTracker_Instead_Of_Update]
ON [IE_PAS].[dbo].[Pharmacy_Tracker_Status]

INSTEAD OF UPDATE

AS

BEGIN

SET NOCOUNT ON;

INSERT INTO [IE_PAS].dbo.prescription_status_trigger_table
(
CollectionStatus
)

SELECT
'This one works'

END

Thanks for your help (or if you just looked and couldn't help, thanks for bothering to take the tinme!)

Chris

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 07:14:46

is the trigger trying to perform DML operation on linked server or on your local server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chris_lunt
Starting Member

25 Posts

Posted - 2013-02-18 : 08:32:40

I'm checking the view that is based on the data from the linked server for an insert or update and if the trigger is fired I want to update a table in the local database where the view exists.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 08:36:20

Are you getting any errors or is it that trigger does nothing? if latter post actual trigger code for us to understand what exactly you're trying to do

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chris_lunt
Starting Member

25 Posts

Posted - 2013-02-18 : 08:45:22

Here's the code for the trigger, I've dpne one for instead of insert and instead of update, I've removedf the inserted and update tables and I'm just trying to do the insert below as a test.

ALTER TRIGGER [dbo].[TriggerPharmacyTracker_Instead_Of_Update]
ON [IE_PAS].[dbo].[Pharmacy_Tracker_Status]

INSTEAD OF UPDATE

AS

BEGIN

SET NOCOUNT ON;

INSERT INTO [IE_PAS].dbo.prescription_status_trigger_table
(
CollectionStatus
)

SELECT
'This one works'

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 09:51:58

where are you using linkedserver here?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chris_lunt
Starting Member

25 Posts

Posted - 2013-02-18 : 10:03:16

The view [IE_PAS].[dbo].[Pharmacy_Tracker_Status] is based on tables from a linked server. I've then out the triggers on that view.

Does that make sense?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 10:11:52

Nope..I cant see where you're specifying linked server here

linkedserver table should be accessed in form server.db.schema.table i cant see server part in your posted query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chris_lunt
Starting Member

25 Posts

Posted - 2013-02-18 : 10:18:05

Apologies for not explkaining myself very well - [IE_PAS].[dbo].[Pharmacy_Tracker_Status] is a view, the create view statement selects fields from tables from the linked server i.e. select * from [linked_server].database.dbo.table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 10:24:43

what does it happen when you run below statement?


INSERT INTO [IE_PAS].dbo.prescription_status_trigger_table
(
CollectionStatus
)

SELECT
'This one works'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chris_lunt
Starting Member

25 Posts

Posted - 2013-02-18 : 10:28:38

Works fine - just inserts the row

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 10:30:04

and howzz your update statement?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chris_lunt
Starting Member

25 Posts

Posted - 2013-02-18 : 10:41:51

Exactly the same

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 10:52:16

same as what? I cant see any update statement posted by you so far.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chris_lunt
Starting Member

25 Posts

Posted - 2013-02-18 : 11:00:26

Within the trigger for the instead of update I've hardcoded the same insert statement that I'm using for the instead of insert trigger.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 12:32:18

i dont understand what you're talking about

I was asking on the main update statement which triggered this trigger.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chris_lunt
Starting Member

25 Posts

Posted - 2013-02-20 : 04:57:08

Sorry Mate, I was off yesterday. I'm confused still by what you're asking. The view I've got on my server just looks at tables on the remote server, I don;t know how the remote server is updated but do know that rows in my view are being updated / inserted.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-02-20 : 05:04:57

A trigger on a view won't fire on changes to the base tables.
I guess that is your problem!?

Too old to Rock'n'Roll too young to die.

chris_lunt
Starting Member

25 Posts

Posted - 2013-02-20 : 05:18:57

Reaslly?
So why can we make triggers on views?
What would normally fire them?
Why do books go on about the INSTEAD OF triggers for views?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-02-20 : 05:22:15

see here: http://msdn.microsoft.com/en-us/library/ms180800.aspx

Too old to Rock'n'Roll too young to die.

chris_lunt
Starting Member

25 Posts

Posted - 2013-02-20 : 05:37:40

I see. So the use is to enable people to update the view and then handle updating the appropriate tables accordingly?

Ah well - back to the drawing board.

Thanks for your help.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources