1

I am having some problems with my TRIGGER:

CREATE TRIGGER "tblSettingTable_INSERT"
ON dbo.tblSettingTable
FOR INSERT
    AS

INSERT INTO dbo.tblSettingReportParameter (tableName, columnName, columnCaption)
SELECT   tableName = CAST(OBJECT_NAME(c.object_id) AS varchar(100)),
            columnName = CAST(c.name AS varchar(100)),
            columnCaption = CAST(ex.value AS varchar(100))
FROM        sys.columns c
LEFT OUTER JOIN sys.extended_properties ex
            ON ex.major_id = c.object_id 
            AND ex.minor_id = c.column_id  
            AND ex.name = 'MS_Caption'
INNER JOIN  inserted ON OBJECT_NAME(c.object_id) = inserted.tableName
WHERE      OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0  
         AND OBJECT_NAME(c.object_id) = inserted.tableName

I am trying the get some column properties from a table and INSERT it into dbo.tblSettingReportParameter but I get this thrown at my face: "Key column information is insufficient or incorrect. Too many rows were affected by update."

What am I doing wrong? Using MS-SQL 2008 RS.

Thanks,

Stefan

unitario
  • 6,295
  • 4
  • 30
  • 43

1 Answers1

8

Should be fixed if you add SET NOCOUNT ON to the trigger.

The xx rows returned is confusing Access (which I assume issues the INSERT to SQL Server of course based on the tags for the question)

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676