CODEWITHSUNDEEP
sql-servert-sqlprintingtriggers
igorGIS
igorGIS

Reputation: 1956

SQL Server Management Studio - PRINT from trigger not showing

I've confused with behavior of Management Studio. I've created a simple trigger for testing (UPDATE action), it should show message (print) to Messages Tab, but no Messages Tab is appear and I can't see any output. If I execute any other query it is ok I could see result tab and messages Tab. The trigger is works itself (data is changing).

Here is my code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[Trigger1]
ON [dbo].[TestTable]
FOR UPDATE
AS
    PRINT ('YAHOOOOO!!!') /* can't see this message */
    UPDATE TestTable
    SET    TestData = 'testitem'
    WHERE  (TestId = 2)

Upvotes: 1

Views: 10411

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107766

2 problems

  1. Your trigger will cause a "too many recursions" error because it tries to update the table (within trigger) which calls the trigger again, which then tries to update the table, then calls the trigger... ad infinitum (in theory). In practice, SQL Server kills it after detecting the error.

  2. The "Edit Top 200 rows" is a special feature that has its own messages window, so you cannot see any output. You need to run your code in a new Query window.

In a new query window, redefine the trigger as follows:

ALTER TRIGGER [dbo].[Trigger1]
ON [dbo].[TestTable]
FOR UPDATE
AS
    PRINT 'YAHOOOOO!!!'; /* can't see this message */
GO

Then, run this UPDATE statement on its own.

UPDATE TestTable
SET    TestData = 'testitem'
WHERE  TestId = 2;

Note: Drop the extraneous brackets.

Upvotes: 2

Related Questions