issue with schemas and triggers

mylesjj

Hi,

I am using version 7.0.0.835 of SQLCompare and have noticed what appears to be a bug when comparing tables with triggers. We have various tables with triggers that belong to a specific schema e.g.

ALTER TRIGGER [R1].[trg_AS_PreventDeletes]
ON [R1].[MyTable]
FOR DELETE
NOT FOR REPLICATION
AS
BEGIN
---
---
END

The schemas are not getting recognised when using SQLCompare to compare the tables against our source control scripts in TFS. The tool scripts the triggers out using the dbo schema rather than the specified R1 schema e.g.:

CREATE TRIGGER [dbo].[trg_AS_PreventDeletes]
ON [dbo].[MyTable]
FOR DELETE
NOT FOR REPLICATION
AS
BEGIN
---
---
END

Regards

Myles Johnson

Simon C

How were the triggers added? A trigger has to have the same schema as the table it is on; if the schema is different in the actual CREATE TRIGGER command SQL Server sets the schema to the table schema in the trigger metadata, but doesn't change the textual definition of the trigger accordingly. SQL Compare uses the textual definition to script the trigger out, hence the trigger schema can be wrong if the textual definition has the wrong schema in it.

You can check the trigger textual definition in the database by the following query on 2005/8 (a similar one can be used on 2000 using syscomments and sysobjects):

select o.name as Name,
s.name as [Schema],
m.definition
from sys.objects o
join sys.sql_modules m on o.object_id = m.object_id
join sys.schemas s on o.schema_id = s.schema_id
where o.name = '<trigger_name_without_schema>'