Update Trigger help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update Trigger help

Author

Topic

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-01-14 : 09:42:49

These are my tables:
Table A: ColumnID, ColumnName, ColumnOffice, ColumnComment,...
Table B: ColumnID, ColumnName

What needs to happen: If ColumnID in Table A is edited, update ColumnName in Table A with what is in Table B.

What I have so far:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER trAUpdate
ON [Table A] FOR UPDATE
AS
IF EXISTS (SELECT * FROM inserted i
INNER JOIN deleted d
ON d.[Office] = i.[Office]
WHERE i.ColumnID <> d.ColumnID)
BEGIN
SET NOCOUNT ON;
UPDATE [Table A]
SET [Table A].ColumnName = b.ColumnName
FROM [Table A] a
INNER JOIN [Table B] b
ON b.ColumnID = a.ColumnID
WHERE a.ColumnID = (SELECT i.ColumnID FROM inserted i)
END
GO

Error I'm getting:
"This row was successfully committed to the database.
However, a problem occurred when attempting to retrieve the data back after the commit.
Because of this, the displayed data in this row is read-only.
To fix the problem, please re-run the query."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-14 : 09:56:29

do like this:-

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER trAUpdate
ON [Table A] FOR UPDATE
AS
SET NOCOUNT ON;
IF UPDATE(ColumnID)
BEGIN
UPDATE [Table A]
SET [Table A].ColumnName = b.ColumnName
FROM [Table A] a
INNER JOIN [Table B] b
ON b.ColumnID = a.ColumnID
INNER JOIN inserted i
ON i.ColumnID = b.ColumnID
END
GO

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-01-14 : 11:28:11

That did not work. I still get the same error.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-14 : 11:34:46

did you check whether update happened?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-14 : 11:52:31

Also try after executing this command :-

ALTER DATABASE yourDB
SET RECURSIVE_TRIGGERS OFF

this will prevent recursive trigger actions

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-01-14 : 11:52:34

quote:
Originally posted by visakh16

did you check whether update happened?

Table A
Row ID Name Comment
1 4 YellowTeam Notes
2 5 RedTeam Notes
5 6 BlueTeam Notes

Table B
4 YellowTeam
5 RedTeam
6 BlueTeam

What's happening is in Table A when I change the ID of 4 to 5, the YellowTeam is not changed to RedTeam. The error comes up, the row turns grey and then when you exit and go back into the table, the row is no longer there.

In my original trigger while I was testing, I had it working partially. When I changed the ID, it changed the name but it changed ALL the names in all rows regardless of ID.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-14 : 12:07:11

quote:
Originally posted by andros30

quote:
Originally posted by visakh16

did you check whether update happened?

Table A
Row ID Name Comment
1 4 YellowTeam Notes
2 5 RedTeam Notes
5 6 BlueTeam Notes

Table B
4 YellowTeam
5 RedTeam
6 BlueTeam

What's happening is in Table A when I change the ID of 4 to 5, the YellowTeam is not changed to RedTeam. The error comes up, the row turns grey and then when you exit and go back into the table, the row is no longer there.

In my original trigger while I was testing, I had it working partially. When I changed the ID, it changed the name but it changed ALL the names in all rows regardless of ID.

Guess it may be due to recursive action . Can you try setting it to off and then executing the update?

ALTER DATABASE dbname
SET RECURSIVE_TRIGGERS OFF

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-01-15 : 15:24:28

quote:

ALTER DATABASE dbname
SET RECURSIVE_TRIGGERS OFF

When I add this into the trigger, it doesn't allow me to execute. The error is "RECURSIVE_TRIGGERS is not a recognized option.".

Let me clarify also that in Table A there may be multiple rows with the same name and ID. The trigger is supposed to only change the name in the row that the ID is edited.

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-01-17 : 09:31:01

quote:
Originally posted by visakh16

do like this:-

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER trAUpdate
ON [Table A] FOR UPDATE
AS
SET NOCOUNT ON;
IF UPDATE(ColumnID)
BEGIN
UPDATE [Table A]
SET [Table A].ColumnName = b.ColumnName
FROM [Table A] a
INNER JOIN [Table B] b
ON b.ColumnID = a.ColumnID
INNER JOIN inserted i
ON i.ColumnID = b.ColumnID
END
GO

For some reason this trigger is working but only through an interface. However, if you attempt to change the values directly from the database then you get the error that I mentioned. Any clues as to why that is?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources