Author |
Topic |
chris_lunt
Starting Member
25 Posts |
Posted - 2013-02-18 : 06:59:16
|
Hello FolksI'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 UPDATEAS BEGIN SET NOCOUNT ON; INSERT INTO [IE_PAS].dbo.prescription_status_trigger_table ( CollectionStatus ) SELECT 'This one works' ENDThanks 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 MVPhttp://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 MVPhttp://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 UPDATEAS BEGINSET 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 MVPhttp://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 herelinkedserver table should be accessed in form server.db.schema.table i cant see server part in your posted query------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 MVPhttp://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 MVPhttp://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 MVPhttp://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 aboutI was asking on the main update statement which triggered this trigger.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 |
|
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
|