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!)
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.
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/
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 )
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
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.
Trigger on a view from a linked server
Posted - 02/18/2013 : 06:59:16
