Red Gate forums :: View topic - synchronization script
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

synchronization script

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
rgfriend



Joined: 12 Jun 2011
Posts: 11

PostPosted: Fri Jun 08, 2012 12:24 am    Post subject: synchronization script Reply with quote

I use SQL compare to compare my dev database with production.

Since I only modify a column 's length to a table, it shows the table schema is different, and all is fine until I get to the generate script screen, I see the script also includes alter trigger of the table.
Nothing actaully changed in the trigger.
And in the review dependency screen, nothing shows there.

So I don't understand why the script include alter trigger statement.

I think the script should only shows the alter column statement.

Thanks
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6641

PostPosted: Mon Jun 11, 2012 2:44 pm    Post subject: Reply with quote

I would understand why the trigger would not show up as a dependency, because triggers are considered part of the table definition rather than a free-standing object.

The only reason for modifying the trigger that I can think of is if the modified column is somehow involved in the trigger definition.
Back to top
View user's profile Send private message
rgfriend



Joined: 12 Jun 2011
Posts: 11

PostPosted: Mon Jun 11, 2012 4:20 pm    Post subject: Reply with quote

But I only change the length of the column.

I didn't see why there is a need to refresh the trigger.

So is this a bug of SQL compare?
Back to top
View user's profile Send private message
ktrainer



Joined: 07 Jun 2012
Posts: 4

PostPosted: Mon Jun 11, 2012 7:40 pm    Post subject: Reply with quote

Hi rgfriend,

I would say no it is not a bug if the trigger is indeed dependent upon the modified column. It seems reasonable to me that SQL compare would include the trigger as it does not know what the trigger actually does and what effect modifying the column has on the trigger. In your case probably nothing, since you are increasing the length of the column, but what if you decrerased the size? Then there may be an issue.

Just my two cents...
Back to top
View user's profile Send private message
rgfriend



Joined: 12 Jun 2011
Posts: 11

PostPosted: Mon Jun 11, 2012 9:11 pm    Post subject: Reply with quote

Thanks, but even if I decrease the size, I don't think the trigger is affected.

It is just a trigger for update , delete.

Something like below:
I only increase the size of one column for example AccidentType

CREATE trigger [dbo].[Investigation_Trigger] on [dbo].[Investigation] for update, delete
as
set nocount on

insert Investigation (
LogDate,
LogNbr,
WorkgroupId,
CarrierId,
ControlNbr,
SchoolId,
SchoolYear,
StudentId,
InvestigationStatusId,
AccidentType,
CreateDate,
CreatedBy,
ChangeDate,
ChangedBy)
select LogDate,
LogNbr,
WorkgroupId,
CarrierId,
ControlNbr,
SchoolId,
SchoolYear,
StudentId,
InvestigationStatusId,
AccidentType
CreateDate,
CreatedBy,
ChangeDate,
ChangedBy
from deleted

GO
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6641

PostPosted: Tue Jun 12, 2012 9:30 am    Post subject: Reply with quote

If I try the same thing manually in SQL Server, it will not allow me to modify the column and displays a message saying the table must be created.
Code:
/****** Object:  Table [dbo].[Table_1]    Script Date: 06/12/2012 09:21:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table_1](
   [id] [int] NOT NULL,
   [data] [nvarchar](50) NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
   [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TRIGGER dbo.tbtrg1
   ON  dbo.table_1
   for INSERT
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

    -- Insert statements for trigger here
    INSERT INTO table_1 (data) SELECT data+'uhhuh' FROM inserted
END
GO

Change the data column to a length of 150 in the query designer and it says the table needs to be rebuilt. If you script it by hand, though, it works.
Code:
ALTER TABLE dbo.Table_1 ALTER COLUMN data NVARCHAR(150) NOT NULL
There must be some circumstances where it's not allowed to change the column. If you have a script from both sides I would be happy to try and reproduce the issue.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group