Trigger on linked server - SQL Server Forums

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

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Trigger on linked server

Author

Topic

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-09-21 : 06:51:05

Hi,

I have two linked server and col1 in table1 in db1 in server1 refers to col2 in table2 in db2 in server2. I need to create a trigger on delete on table1 in order to check if col1 value exists in col2 deny the delete row
Any idea is appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-21 : 07:15:28

You cant create cross server triggers. Cant you create a ssis pacakge to bring data from one table onto another staging table on the other server and then perform comparison and delete?

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-09-21 : 08:23:29

I've created cross server trigger but something goes wrong that I think it's due to this statement:

delete from table1 where ID = (Select ID from deleted)

I have created INSTEAD OF DELETE trigger but I'm not sure if it understands (Select ID from deleted)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-09-21 : 10:44:27

Could you post the entire trigger code, explain what it's supposed to do and what it is doing.

An instead of delete replaces the delete statement with whatever's in the trigger.

--
Gail Shaw
SQL Server MVP

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-09-23 : 02:03:51

The entire code of my trigger is as follows:

ALTER TRIGGER [trg_ChkID] 
   ON  [tb_Disciplines]
  INSTEAD OF DELETE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
IF EXISTS (SELECT * FROM	/*Name of linked server shall be added here*/ POPricing.tb_PurchaseOrder			
    WHERE POPricing.tb_PurchaseOrder.DisciplineID = ID)
		RAISERROR ('This row cannot be deleted because the ID refers to DisciplineID in POPricing.tb_PurchaseOrder',10, 1)
--else
delete from base.tb_Disciplines where ID = (Select ID from deleted)

END

When a row in tb_Disciplines table is going to be deleted, it's supposed to check if it exists in tb_PurchaseOrder table, which is in another server and raise an error message. And if it doesn't exist in tb_PurchaseOrder delete the row in tb_Disciplines.
but it doesn't delete the row in any case without giving any error!

Thanks Gail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 02:54:07

modify like below:-

ALTER TRIGGER [trg_ChkID] 
   ON  [tb_Disciplines]
  INSTEAD OF DELETE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
IF EXISTS (SELECT * FROM	/*Name of linked server shall be added here*/ POPricing.tb_PurchaseOrder	p
INNER JOIN DELETED d
ON d.ID=POPricing.tb_PurchaseOrder.DisciplineID)
		RAISERROR ('This row cannot be deleted because the ID refers to DisciplineID in POPricing.tb_PurchaseOrder',10, 1)
--else
delete from base.tb_Disciplines where ID = (Select ID from deleted)

END

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-09-23 : 03:37:46

I modified it according to your comments but the same problem still exists!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-09-23 : 04:15:46

Uncomment the else (if I understand your requirement correctly, that is)

If (row exists on remote server)
 raiserror
else
 delete

--
Gail Shaw
SQL Server MVP

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-09-23 : 04:21:28

Thank you Gail and Visakh for your replies
I'd already uncommented the else but no result yet.
There's no difference between your code and Visakh's, Gail, is there?

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-09-23 : 06:31:29

I modified this line of code and it works fine now

delete from base.tb_Disciplines WHERE ID = (SELECT deleted.ID FROM deleted)

Thanks all again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources