SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Trigger on a view from a linked server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chris_lunt
Starting Member

25 Posts

Posted - 02/18/2013 :  06:59:16  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/18/2013 :  07:14:46  Show Profile  Reply with Quote
is the trigger trying to perform DML operation on linked server or on your local server?

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

Go to Top of Page

chris_lunt
Starting Member

25 Posts

Posted - 02/18/2013 :  08:32:40  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  08:36:20  Show Profile  Reply with Quote
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/

Go to Top of Page

chris_lunt
Starting Member

25 Posts

Posted - 02/18/2013 :  08:45:22  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  09:51:58  Show Profile  Reply with Quote
where are you using linkedserver here?

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

Go to Top of Page

chris_lunt
Starting Member

25 Posts

Posted - 02/18/2013 :  10:03:16  Show Profile  Reply with Quote
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?

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  10:11:52  Show Profile  Reply with Quote
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/

Go to Top of Page

chris_lunt
Starting Member

25 Posts

Posted - 02/18/2013 :  10:18:05  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  10:24:43  Show Profile  Reply with Quote
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/

Go to Top of Page

chris_lunt
Starting Member

25 Posts

Posted - 02/18/2013 :  10:28:38  Show Profile  Reply with Quote
Works fine - just inserts the row
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  10:30:04  Show Profile  Reply with Quote
and howzz your update statement?

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

Go to Top of Page

chris_lunt
Starting Member

25 Posts

Posted - 02/18/2013 :  10:41:51  Show Profile  Reply with Quote
Exactly the same
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  10:52:16  Show Profile  Reply with Quote
same as what? I cant see any update statement posted by you so far.

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

Go to Top of Page

chris_lunt
Starting Member

25 Posts

Posted - 02/18/2013 :  11:00:26  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  12:32:18  Show Profile  Reply with Quote
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/

Go to Top of Page

chris_lunt
Starting Member

25 Posts

Posted - 02/20/2013 :  04:57:08  Show Profile  Reply with Quote
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.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 02/20/2013 :  05:04:57  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

chris_lunt
Starting Member

25 Posts

Posted - 02/20/2013 :  05:18:57  Show Profile  Reply with Quote
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?
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 02/20/2013 :  05:22:15  Show Profile  Visit webfred's Homepage  Reply with Quote
see here: http://msdn.microsoft.com/en-us/library/ms180800.aspx


Too old to Rock'n'Roll too young to die.
Go to Top of Page

chris_lunt
Starting Member

25 Posts

Posted - 02/20/2013 :  05:37:40  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000