February 4, 2009 at 6:32 am
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 * FROMdbname.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?
February 4, 2009 at 6:35 am
Your trigger is referencing another database:
dbname.dba.tb_table2
If that database does not exist on the second server - or has different data, your trigger may bahave differently.
This is one reason that using 4 part names in a trigger can be risky. It means that your transaction depends on another database that may even be missing.
February 4, 2009 at 6:42 am
Are you sure the user that is executing the source query has rights on the other database? Cross-Database ownership chaining is disabled by default in SQL Server 2005.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 4, 2009 at 6:43 am
The database exists. besides I test the trigger on data that exists on both databases. The trigger acts the same if the referenced column data exists on the other database or not.
February 4, 2009 at 6:49 am
Jack Corbett (2/4/2009)
Are you sure the user that is executing the source query has rights on the other database? Cross-Database ownership chaining is disabled by default in SQL Server 2005.
The user has DBA rights so I don't think it's due to cross-database ownership
February 4, 2009 at 6:58 am
peace2007 (2/4/2009)
Jack Corbett (2/4/2009)
Are you sure the user that is executing the source query has rights on the other database? Cross-Database ownership chaining is disabled by default in SQL Server 2005.The user has DBA rights so I don't think it's due to cross-database ownership
DBA rights on BOTH databases?
February 4, 2009 at 7:15 am
Have you verified that the query in the NOT EXISTS is correct?
You do have a couple of flaws in the T-SQL for the trigger as well.
2. If you have a multi-row delete the delete code will only delete 1 row and you are not guaranteed which row it will be.
A possibly better solution might be this:
alter TRIGGER base.trg_name ON base.tb_table1
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON ;
DELETE T
FROM
base.tb_table1 T
WHERE
NOT EXISTS (SELECT 1 FROM deleted D JOIN dbname.dba.tb_table2 P
ON D.id = P.vid WHERE D.id = T.id)
IF @@ROWCOUNT = 0
BEGIN
RAISERROR('Delete not executed because rows exist
in dbname.dba.tb_table2 P', 10, 1)
END
END
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 6, 2009 at 11:06 pm
Thanks Jack for the detailed comment š
I hadn't considered the case you described about multi row delete, I'll apply the required changes. However, the problem I'd written exists with one row and the trigger works fine in the same database located in another server. Actually, the problem occurs when I manually delete one row from a table!
February 7, 2009 at 1:37 am
the main problem was due to not checking another column value;)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply