12

Shooting Yourself In The Foot While Testing Triggers, or The Importance of GO |...

 3 years ago
source link: https://thelonedba.wordpress.com/2021/02/27/shooting-yourself-in-the-foot-while-testing-triggers-or-the-importance-of-go/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Shooting Yourself In The Foot While Testing Triggers, or The Importance of GO

No, not “Go” the game…

I was working on some functionality for a create table trigger. I had the whole creation / testing thing wrapped up in a single script for easy testing and possible deployment across multiple databases:

USE Testing
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE name = 'NewTableTrigger')
EXEC ('CREATE TRIGGER NewTableTrigger ON DATABASE FOR CREATE_TABLE AS BEGIN SELECT 1 END')
GO
ALTER TRIGGER NewTableTrigger ON DATABASE FOR CREATE_TABLE
AS
BEGIN
PRINT 'Create Table Trigger Fired'
END
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'TestTable')
EXEC ('DROP TABLE TestTable');
GO
CREATE TABLE TestTable (i INT);

All well & good. (Obviously, the functionality in the trigger was a little more complicated than a print statement…)

Let’s test that script:

Create Table Trigger Fired

Completion time: 2021-02-27T14:17:09.4783931+00:00

Great. Refresh the tables list in the object explorer to go poking around, and:

Hmm. We have a problem.

Add a line to the script to see if anything’s going on there:

USE Testing
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE name = 'NewTableTrigger')
EXEC ('CREATE TRIGGER NewTableTrigger ON DATABASE FOR CREATE_TABLE AS BEGIN SELECT 1 END')
GO
ALTER TRIGGER NewTableTrigger ON DATABASE FOR CREATE_TABLE
AS
BEGIN
PRINT 'Create Table Trigger Fired'
END
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'TestTable')
EXEC ('DROP TABLE TestTable');
GO
CREATE TABLE TestTable (i INT);
SELECT * FROM dbo.TestTable

And run…:

Create Table Trigger Fired
Msg 208, Level 16, State 1, Line 21
Invalid object name 'dbo.TestTable'.

Completion time: 2021-02-27T14:24:44.0301750+00:00

Not just me failing to see it, then.

After some step-by-step testing, I finally found the problem:

USE Testing
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE name = 'NewTableTrigger')
EXEC ('CREATE TRIGGER NewTableTrigger ON DATABASE FOR CREATE_TABLE AS BEGIN SELECT 1 END')
GO
ALTER TRIGGER NewTableTrigger ON DATABASE FOR CREATE_TABLE
AS
BEGIN
PRINT 'Create Table Trigger Fired'
END
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'TestTable')
EXEC ('DROP TABLE TestTable');
GO
CREATE TABLE TestTable (i INT);
SELECT * FROM dbo.TestTable

Yup. That one “GO” missing at the end of the ALTER TRIGGER statement was causing the next part of the script to be executed as part of the same batch, meaning it was part of the trigger code, and the trigger was deleting the table that I was attempting to create.

Advertisements
Report this ad

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK