trigger doesn't work well - 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

SQL Server Administration (2005)

trigger doesn't work well

Author

Topic

Peace2007
Posting Yak Master

239 Posts

Posted - 2009-02-04 : 08:29:12

I’m having a trigger on delete which runs perfectly on a database. However, it does not work on the same database, which locates on another server! It prevents deleting a row in any case.
The trigger is to prevent deleting a row if a column exists in a table in another database, which locates in the same server.

alter TRIGGER base.trg_name
   ON  base.tb_table1
    INSTEAD OF DELETE
AS 
BEGIN
	SET NOCOUNT ON;
IF NOT EXISTS (SELECT * FROM	dbname.dba.tb_table2 P INNER JOIN DELETED d ON d.ID = P.VID) 
begin
	delete from base.tb_table1 WHERE ID= (SELECT deleted.ID FROM deleted)
end	
ELSE
begin
	RAISERROR ('This row cannot be deleted',10, 1)
end 
END

Could you tell me what may cause this trigger not to work well?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 08:31:55

can you check if the trigger is enabled in the db where it doesnt work?

Peace2007
Posting Yak Master

239 Posts

Posted - 2009-02-04 : 08:43:37

yes it's enabled

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-04 : 08:46:09

Do this trigger become nested or recursive?
Check setting for "NESTED triggers" and "RECURSIVE triggers", both at server level and database level for both servers.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 09:05:07

better to change it like this as your deleted table may contain more than one record at a time


alter TRIGGER base.trg_name
   ON  base.tb_table1
    INSTEAD OF DELETE
AS 
BEGIN
	SET NOCOUNT ON;
IF NOT EXISTS (SELECT * FROM	dbname.dba.tb_table2 P INNER JOIN DELETED d ON d.ID = P.VID) 
begin
	 delete t 
         from base.tb_table1 t 
         join deleted d 
         on t.ID= d.ID 
end	
ELSE
begin
	RAISERROR ('This row cannot be deleted',10, 1)
end 
END

Peace2007
Posting Yak Master

239 Posts

Posted - 2009-02-07 : 03:37:32

Thanks Visakh I've applied the changes you commented to prevent problems with multiple rows
but the main problem was due to not checking another column value

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 09:30:13

ok..thats fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources