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".