SQL Prompt warns about UPDATE triggers

GregWilson

When running a TSQL statement similar to:

CREATE TRIGGER [dbo].[trgPersonalFinancialHeaderModified]
	ON [dbo].[PersonalFinancialHeader] AFTER UPDATE NOT FOR REPLICATION
AS
	UPDATE d  SET Modified = getdate() 
	FROM [dbo].[PersonalFinancialHeader] d inner join inserted i on d.Modified = i.Modified AND d.[AccountNum] = i.[AccountNum] AND d.[StatementDate] = i.[StatementDate];

SQL Prompt throws a warning pop-up: "Execution warning   You're about to execute an UPDATE statement without a WHERE clause on: PersonalFinancialHeader d"

Two issues:

1. I'm not executing an UPDATE statement, I'm executing a CREATE TRIGGER statement
2. A trigger UPDATE statement, when inner joined to the "inserted" pseudo-table has no need for a where clause, making this warning ridiculous.

How can I stop these warnings while continuing to get this warning when I am actually trying to do an UPDATE statement with no WHERE clause?

lgivens

Hi Greg,
 
Sorry for the delayed response, but I wanted to get a second set of eyes on what you were seeing. I don't know if it's good news or bad news, but I was able to duplicate the scenario you described. I have escalated to the development team so they can determine the correct course of action to provide a resolution. In the interim, there are options available to you.

• You could do nothing, simply click on the button to have the code execute anyway. (this is probably the least attractive option)
• You could disable the rule that is triggering (SQL Prompt > Manage Code Analysis Rules > Uncheck BP018 Update Statement...)
• You could click on the checkbox on the popup window to not show the warning anymore.

 
The last option leaves the rule active and inplace, it just doesn't warn you if you try to execute code that 'breaks' a rule. See the screenshot below, your CREATE TRIGGER isn't showing any errors, while my UPDATE statement does show the green squiggly line that indicates a rule violation.
 

 
I hope this helps. Please let me know if you have any further questions.
 
Thank you,
 
Lee Givens
Product Support Engineer