Trying to trigger an alternative update on table

Question

Hi All

Im basically using a SDK that tries to update a whole table row even when I am trying to update only one specific field.  The problem is that one of the fields is an identity column so the update cannot go through.

I have been trying to create a trigger that will fire when an update of the counter (i.e identity column) is sent, take the information from the created "inserted" table and carry out the update.

I have listed below what I have so far that does not work

USE [***]
GO
/****** Object:  Trigger [dbo].[OnUpdateOfCounter]    Script Date: 04/10/2012 16:47:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[OnUpdateOfCounter] ON
[dbo].[Customer] 
FOR UPDATE AS
SET NOCOUNT ON
IF ( UPDATE(Counter))
BEGIN
  UPDATE [Customer] SET [Customer].[CustomerName] = inserted.[CustomerName]
FROM inserted
WHERE [Customer].[CustomerCode] = inserted.[CustomerCode]
END

---

Dionne

Answer 1

Well, you can technically create INSTEAD OF trigger on the table. Not very good idea but if you're stuck with SDK..

create table dbo.T
(
	ID int not null identity(1,1) primary key,
	Val int not null
)
go

insert into dbo.T(Val) values(1),(2)
go

create trigger trgInsteadOfUpdate on dbo.T
instead of update
as
begin
	if @@ROWCOUNT = 0
		return
		
	update dbo.T
	set Val = inserted.Val
	from 
		dbo.T join inserted on
			dbo.T.ID = inserted.id
end
go

update dbo.T set Val = 1000 where ID = 1

if ID can also be modified (and you want to avoid it), you need to grab ID based on deleted table (add it as 3rd join). 

---

Thank you!

My blog: http://aboutsqlserver.com

Answer 2

Hi Dmitri

Thanks for your response.  Unfortunately Im getting the following error

Msg 2113, Level 16, State 1, Procedure trgInsteadOfUpdate, Line 9

Cannot create INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'trgInsteadOfUpdate' on table 'Customer'. This is  because the table has a FOREIGN KEY with cascading DELETE or UPDATE.

The Identity Column is not the primary key - not sure if thats why this error is thrown.

I cannot change any of the table / field constraints in the database as this is linked to another system.

Kind regards

Dionne

---

Dionne

Answer 3

Hi Dangerous Dave81,

>> Msg 2113 Cannot create INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER on table. This is because the table has a FOREIGN KEY with cascading DELETE or UPDATE.

Regarding to the error message, this is restriction in T-SQL is that there cannot be both an INSTEAD OF UPDATE/DELETE trigger and a FOREIGN KEY constraint with ON UPDATE/DELETE CASCADE.

In order to do the cascading, a delete is issued on the child table, and if that is preempted by an INSTEAD OF trigger, the cascading may fail. You can try to use an AFTER trigger and select out of the DELETED table. The DELETED table holds the values before the change and the INSERTED will be the new values.

Alternatively you can follow the answer here related to this error.

---

Regards, Amber zhang

Answer 4

Thanks for your response Amber

Do you know if this can be specified for a particular column update in the table?

I set my query as below to what I thought would only fire when there was an attempt to update the Counter (identity field which cannot be updated) but it just fires for any update now and the field which I am trying to set - i.e. the IsProcessed field remains set to true no matter what I try to set it to.

Kind regards

Dionne

USE [*****]
GO
/****** Object:  Trigger [dbo].[tgInsteadOfUpdate]    Script Date: 04/12/2012 11:22:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tgInsteadOfUpdate]
ON [dbo].[CustomerSalesOrder]
INSTEAD OF UPDATE
AS
SET IDENTITY_INSERT CustomerSalesOrder ON
IF UPDATE(Counter)
UPDATE CustomerSalesOrder SET IsProcessed = inserted.Isprocessed
FROM inserted

---

Dionne

Answer 5

A trigger fires on a table, not on a column.  So the trigger will fire on the table no matter which column was updated.

The IF UPDATE code is just an internal filter in how to process data through the trigger.  Since I do not see what is happening with the Isprocessed value, I cannot fully know.  However, if you update the Identity column and the Isprocessed column or only the Isprocessed column, then would expect it to work.  (Sample test script follows.)

USE tempdb
go
CREATE TABLE dbo.atest (kv INT IDENTITY, dt VARCHAR(50));
go
CREATE TRIGGER kv_update ON dbo.atest  INSTEAD OF UPDATE
AS
SET IDENTITY_INSERT dbo.atest ON
IF UPDATE(kv) 
UPDATE dbo.atest SET dt = atest.dt + inserted.dt
FROM inserted;
go
INSERT INTO dbo.atest (dt) VALUES('Note')
go
SELECT * FROM dbo.atest -- Note
go
UPDATE dbo.atest SET kv = kv + 1 -- Try to update PK
go
SELECT * FROM dbo.atest -- NoteNote
go
UPDATE dbo.atest SET kv = kv + 1, dt='book' -- Try to update PK
go
SELECT * FROM dbo.atest -- NoteNotebook
go
DROP TABLE dbo.atest
GO

Perhaps this simplified test structure can help you work out what is happening in your case.

RLF