synchronization script

Comments

• Options

  Brian Donahue Posts: 6,590 Bronze 1

  June 11, 2012 9:44AM

  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.

  0 · Share on Twitter
• Options

  rgfriend Posts: 21 Bronze 2

  June 11, 2012 11:20AM

  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?

  0 · Share on Twitter
• Options

  ktrainer Posts: 6

  June 11, 2012 2:40PM

  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...

  0 · Share on Twitter
• Options

  rgfriend Posts: 21 Bronze 2

  June 11, 2012 4:11PM

  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

  0 · Share on Twitter
• Options

  Brian Donahue Posts: 6,590 Bronze 1

  June 12, 2012 4:30AM

  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.

  /****** 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.

  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.

  0 · Share on Twitter