Geek Answers Handbook

SQL Server: Unable to update column in table.

I cannot understand why my column is not being updated.

I added a new column to the existing table as follows:

ALTER TABLE dbname..tablename ADD RejectedCode [varchar](5) NULL

When I run the query below:

 UPDATE dbname..tablename SET RejectedCode = 'OTHER', Notes = 'DEBUG' WHERE RecordID = 12345

He says:

 (1 row(s) affected)

I check the corresponding entry and I see the Notes column, but the new column still displays NULL and I am not getting an error message.

What am I doing wrong? Many thanks for the help.

+6
source share
3 answers

It looks like you have a trigger of instead of update on your table

+3
source

Instead of updating the dynamic query directly, as shown below,

  EXEC('Update dbname..tablename SET RejectedCode = ''OTHER'', Notes = ''DEBUG'' WHERE RecordID = 12345')
+1
source

It looks like there is an INSTEAD OF UPDATE trigger on your table. To find out the triggers associated with the table are executed as shown below.

In the object explorer, navigate to your table name and click node triggers.

or use the code below

 SELECT sysobjects.name AS trigger_name ,USER_NAME(sysobjects.uid) AS trigger_owner ,s.name AS table_schema ,OBJECT_NAME(parent_obj) AS table_name ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] FROM sysobjects INNER JOIN sysusers ON sysobjects.uid = sysusers.uid INNER JOIN sys.tables t ON sysobjects.parent_obj = t.object_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE sysobjects.type = 'TR' and s.name = 'your table name'
+1
source

More articles:


All Articles