T-SQL trigger to update columns

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
7
down vote

favorite

I've been working with SQL queries for a couple of weeks and I'm trying to learn and understand from my mistakes. I have a table with two columns IsInvisible (bit) and ShortName (string) and when one is changed to update the other one also like: - if added a ~ in front of the ShortName to set IsInvisible to 1 and if ~ is removed to set it to 0 - if IsInvisible is set to 1 to add ~ in front of the ShortName and when set to 0 to remove it.

I've tried something like:

ALTER TRIGGER [dbo].[updateInvisibility]
ON [dbo].[table]
AFTER UPDATE
AS
BEGIN

UPDATE t
SET IsInvisible = (CASE WHEN i.ShortName like '~%' THEN 1 ELSE 0 END),
 ShortName = (CASE WHEN i.IsInvisible = 1 AND t.ShortName NOT LIKE '~%'
 THEN '~' + t.ShortName
 ELSE t.ShortName
 END)
FROM table t JOIN
 inserted i
 ON t.Id = i.Id;

end

My problem is that when I'm updating one of the columns or both, nothing happens and I get this error:

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

What am I doing wrong?

The "Allow Triggers to Fire Others" option displays "True".

edited Aug 7 at 10:46

Paul Whiteâ™¦

45.7k14246392

asked Aug 6 at 16:09

Dana

1402

Do you have any other triggers on this table?
â€“Â Shaulinator
Aug 6 at 16:34

add a commentÂ |Â

up vote
7
down vote

favorite

I've tried something like:

ALTER TRIGGER [dbo].[updateInvisibility]
ON [dbo].[table]
AFTER UPDATE
AS
BEGIN

UPDATE t
SET IsInvisible = (CASE WHEN i.ShortName like '~%' THEN 1 ELSE 0 END),
 ShortName = (CASE WHEN i.IsInvisible = 1 AND t.ShortName NOT LIKE '~%'
 THEN '~' + t.ShortName
 ELSE t.ShortName
 END)
FROM table t JOIN
 inserted i
 ON t.Id = i.Id;

end

My problem is that when I'm updating one of the columns or both, nothing happens and I get this error:

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

What am I doing wrong?

The "Allow Triggers to Fire Others" option displays "True".

edited Aug 7 at 10:46

Paul Whiteâ™¦

45.7k14246392

asked Aug 6 at 16:09

Dana

1402

Do you have any other triggers on this table?
â€“Â Shaulinator
Aug 6 at 16:34

add a commentÂ |Â

up vote
7
down vote

favorite

I've tried something like:

ALTER TRIGGER [dbo].[updateInvisibility]
ON [dbo].[table]
AFTER UPDATE
AS
BEGIN

UPDATE t
SET IsInvisible = (CASE WHEN i.ShortName like '~%' THEN 1 ELSE 0 END),
 ShortName = (CASE WHEN i.IsInvisible = 1 AND t.ShortName NOT LIKE '~%'
 THEN '~' + t.ShortName
 ELSE t.ShortName
 END)
FROM table t JOIN
 inserted i
 ON t.Id = i.Id;

end

My problem is that when I'm updating one of the columns or both, nothing happens and I get this error:

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

What am I doing wrong?

The "Allow Triggers to Fire Others" option displays "True".

edited Aug 7 at 10:46

Paul Whiteâ™¦

45.7k14246392

asked Aug 6 at 16:09

Dana

1402

I've tried something like:

ALTER TRIGGER [dbo].[updateInvisibility]
ON [dbo].[table]
AFTER UPDATE
AS
BEGIN

UPDATE t
SET IsInvisible = (CASE WHEN i.ShortName like '~%' THEN 1 ELSE 0 END),
 ShortName = (CASE WHEN i.IsInvisible = 1 AND t.ShortName NOT LIKE '~%'
 THEN '~' + t.ShortName
 ELSE t.ShortName
 END)
FROM table t JOIN
 inserted i
 ON t.Id = i.Id;

end

My problem is that when I'm updating one of the columns or both, nothing happens and I get this error:

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

What am I doing wrong?

The "Allow Triggers to Fire Others" option displays "True".

edited Aug 7 at 10:46

Paul Whiteâ™¦

45.7k14246392

asked Aug 6 at 16:09

Dana

1402

edited Aug 7 at 10:46

Paul Whiteâ™¦

45.7k14246392

edited Aug 7 at 10:46

Paul Whiteâ™¦

45.7k14246392

edited Aug 7 at 10:46

Paul Whiteâ™¦

45.7k14246392

asked Aug 6 at 16:09

Dana

1402

asked Aug 6 at 16:09

Dana

1402

asked Aug 6 at 16:09

Dana

1402

Do you have any other triggers on this table?
â€“Â Shaulinator
Aug 6 at 16:34

add a commentÂ |Â

Do you have any other triggers on this table?
â€“Â Shaulinator
Aug 6 at 16:34

Do you have any other triggers on this table?
â€“Â Shaulinator
Aug 6 at 16:34

add a commentÂ |Â

1 Answer
1

active

oldest

votes

up vote
9
down vote

accepted

Here is the problem:

You update the table

It triggers the update trigger: updateInvisibility

In the trigger you update the table again so it triggers the updateInvisibility trigger

and then it keeps doing that so you end up triggering the trigger many times in a sort of recursive fashion without even realizing. SQL server will allow this call nesting to be 32 levels deep and then it throws this error:

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

which is pretty clear.

T-SQL Solution

The code-only solution is to check the nesting level and leave if bigger than 1 (1 is the first time it is called by the update):

IF TRIGGER_NESTLEVEL() > 1 RETURN;

Like this:

ALTER TRIGGER [dbo].[updateInvisibility]
ON [dbo].[table]
AFTER UPDATE
AS
BEGIN
 IF TRIGGER_NESTLEVEL() > 1 RETURN;

 -- Do your work...
END

Alternative

As pointed out in a comment by RBarryYoung, you can prevent this problem in the first place by disabling recursive triggers.

edited Aug 7 at 7:56

Andriy M

15.1k53469

answered Aug 6 at 16:43

CodingYoshi

2612

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f214202%2ft-sql-trigger-to-update-columns%23new-answer', 'question_page');

);

Post as a guest

Name

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
9
down vote

accepted

Here is the problem:

You update the table

It triggers the update trigger: updateInvisibility

In the trigger you update the table again so it triggers the updateInvisibility trigger

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

which is pretty clear.

T-SQL Solution

The code-only solution is to check the nesting level and leave if bigger than 1 (1 is the first time it is called by the update):

IF TRIGGER_NESTLEVEL() > 1 RETURN;

Like this:

ALTER TRIGGER [dbo].[updateInvisibility]
ON [dbo].[table]
AFTER UPDATE
AS
BEGIN
 IF TRIGGER_NESTLEVEL() > 1 RETURN;

 -- Do your work...
END

Alternative

As pointed out in a comment by RBarryYoung, you can prevent this problem in the first place by disabling recursive triggers.

edited Aug 7 at 7:56

Andriy M

15.1k53469

answered Aug 6 at 16:43

CodingYoshi

2612

add a commentÂ |Â

up vote
9
down vote

accepted

Here is the problem:

You update the table

It triggers the update trigger: updateInvisibility

In the trigger you update the table again so it triggers the updateInvisibility trigger

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

which is pretty clear.

T-SQL Solution

The code-only solution is to check the nesting level and leave if bigger than 1 (1 is the first time it is called by the update):

IF TRIGGER_NESTLEVEL() > 1 RETURN;

Like this:

ALTER TRIGGER [dbo].[updateInvisibility]
ON [dbo].[table]
AFTER UPDATE
AS
BEGIN
 IF TRIGGER_NESTLEVEL() > 1 RETURN;

 -- Do your work...
END

Alternative

As pointed out in a comment by RBarryYoung, you can prevent this problem in the first place by disabling recursive triggers.

edited Aug 7 at 7:56

Andriy M

15.1k53469

answered Aug 6 at 16:43

CodingYoshi

2612

add a commentÂ |Â

up vote
9
down vote

accepted

Here is the problem:

You update the table

It triggers the update trigger: updateInvisibility

In the trigger you update the table again so it triggers the updateInvisibility trigger

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

which is pretty clear.

T-SQL Solution

The code-only solution is to check the nesting level and leave if bigger than 1 (1 is the first time it is called by the update):

IF TRIGGER_NESTLEVEL() > 1 RETURN;

Like this:

ALTER TRIGGER [dbo].[updateInvisibility]
ON [dbo].[table]
AFTER UPDATE
AS
BEGIN
 IF TRIGGER_NESTLEVEL() > 1 RETURN;

 -- Do your work...
END

Alternative

As pointed out in a comment by RBarryYoung, you can prevent this problem in the first place by disabling recursive triggers.

edited Aug 7 at 7:56

Andriy M

15.1k53469

answered Aug 6 at 16:43

CodingYoshi

2612

Here is the problem:

You update the table

It triggers the update trigger: updateInvisibility

In the trigger you update the table again so it triggers the updateInvisibility trigger

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

which is pretty clear.

T-SQL Solution

The code-only solution is to check the nesting level and leave if bigger than 1 (1 is the first time it is called by the update):

IF TRIGGER_NESTLEVEL() > 1 RETURN;

Like this:

ALTER TRIGGER [dbo].[updateInvisibility]
ON [dbo].[table]
AFTER UPDATE
AS
BEGIN
 IF TRIGGER_NESTLEVEL() > 1 RETURN;

 -- Do your work...
END

Alternative

As pointed out in a comment by RBarryYoung, you can prevent this problem in the first place by disabling recursive triggers.

edited Aug 7 at 7:56

Andriy M

15.1k53469

answered Aug 6 at 16:43

CodingYoshi

2612

edited Aug 7 at 7:56

Andriy M

15.1k53469

edited Aug 7 at 7:56

Andriy M

15.1k53469

edited Aug 7 at 7:56

Andriy M

15.1k53469

answered Aug 6 at 16:43

CodingYoshi

2612

answered Aug 6 at 16:43

CodingYoshi

2612

answered Aug 6 at 16:43

CodingYoshi

2612

add a commentÂ |Â

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky