June 2, 2009 at 1:51 am
Hi,
I have written a trigger for a table called test_table2. The trigger should invoke a stored procedure which send out a mail. Unfortunatly it is not working as expected. But the else part in the trigger works as expected.
The code for the trigger is as follows::
CREATE TRIGGER [test_table2_trigger1]
ON [dbo].[Test_Table2]
AFTER INSERT
AS
DECLARE @COUNT INT
SET @COUNT = (SELECT COUNT(*) FROM TEST_TABLE2)
IF @COUNT=2
BEGIN
print @COUNT
EXEC PROC_TEST_TABLE2
END
ELSE
BEGIN
PRINT 'NO'
END
PROCEDURE ::
CREATE PROCEDURE [dbo].[proc_test_table2]
AS
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients=N'vinayak.naik@composys.com',
@body='Test Mail',
@subject = 'SQL Server Trigger Mail',
@query='select * from [testdatabase].[dbo].[test_table2]',
@profile_name = 'DBMailProfile'
END
There is no problem with the stored procedure as it runs well when it is independently executed. Can somebody help me on this please ?????
June 2, 2009 at 5:42 am
Can you explain what should be the expected behavior of the IF block?
What you are trying to do within the trigger?
Aren't you using the main table instead of the virtual table "inserted"?
--Ramesh
June 2, 2009 at 1:40 pm
Syntatically your statement is correct. I would suspect that in every instance the problem you are facing is that the process always executes the ELSE portion. Which as Ramesh points out, your @Count variable is populated by counting the rows in the base table not the number of rows actually inserted...
Try this and see if your results are different:
IF NOT(OBJECT_ID(N'proc_test_table2') is null)
DROP PROCEDURE proc_test_table2
GO
CREATE PROCEDURE [dbo].[proc_test_table2]
AS
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients=N'vinayak.naik@composys.com',
@body='Test Mail',
@subject = 'SQL Server Trigger Mail',
@query='select * from [testdatabase].[dbo].[test_table2]',
@profile_name = 'DBMailProfile'
END
GO
IF NOT(OBJECT_ID(N'test_table2_trigger1') is null)
DROP TRIGGER test_table2_trigger1
GO
CREATE TRIGGER [test_table2_trigger1] ON [dbo].[Test_Table2]
AFTER INSERT
AS
DECLARE @COUNT INT
SELECT @COUNT = count(*) FROM inserted
PRINT convert(varchar,getdate(),121)+' |INFO| this execution inserted '+cast(@COUNT as varchar)+' rows'
IF @COUNT = 2 BEGIN
PRINT @COUNT
EXEC PROC_TEST_TABLE2
END
ELSE BEGIN
PRINT 'NO'
END
GO
-- INSERT INTO test_table2 [Select Statement that matches the column defintions and yields 2 rows]
-- example: INSERT INTO tblTest SELECT TOP 2 name FROM sys.objects
-
June 2, 2009 at 10:26 pm
Thanks for the quick reqply Jason and Ramesh,
I will explain exactly what i would like to do. The table "test_table2" will be initially empty. Records into the table will be inserted at two different times. When the first record is inserted table @count has the value "1". When the second record is inserted @count should have value "2" and the Stored procedure should be executed which is not happening.
That is why i am using the base table then using the virtual table "inserted" because "inserted" will only return me the record's inserted at particular instance. So if i use "inserted" it will always return me "1" as i am inserting only one record at a time.
I want the trigger to execute when the second record is inserted and the table count is then "2".
table structure
Number(int) Value(nvarchar)
1 One
2 Two
June 3, 2009 at 5:15 am
I think the problem here is to do with locking.
The insert process locks the 'second row' when it starts the insert and then the trigger fires. In the trigger it is possible to look at the psuedo inserted tables or the base tables but, as soon as you set off another procedure to do the email I don't think you can use a query that tries to access that locked row. At least the process hangs when I try it.
The @query='select * from [testdatabase].[dbo].[test_table2]' line works if you use @query='select top 1 * from [testdatabase].[dbo].[test_table2]' because it only accesses row 1. (I'm sure more knowledgable readers here will correct me if I'm way off)
It probably doesn't get what you want in the email but it is something to work on.
Starting with Jason's code but referencing only the base table:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test_table2]') AND type in (N'U'))
DROP TABLE [dbo].[test_table2]
GO
create table test_table2 (Number int ,Value varchar(10))
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_test_table2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[proc_test_table2]
GO
CREATE PROCEDURE [dbo].[proc_test_table2]
AS
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients=N'blah@blah',
@body='Test Mail',
@subject = 'SQL Server Trigger Mail',
@query='select top 1 * from [dbo].[test_table2]',
@profile_name = 'blahblah'
END
GO
IF NOT(OBJECT_ID(N'test_table2_trigger1') is null)
DROP TRIGGER test_table2_trigger1
GO
CREATE TRIGGER [test_table2_trigger1] ON [dbo].[Test_Table2]
AFTER INSERT
AS
DECLARE @COUNT INT
SELECT @COUNT = count(*) FROM test_table2
PRINT convert(varchar,getdate(),121)+' |INFO| this execution inserted '+cast(@COUNT as varchar)+' rows'
IF @COUNT = 2
BEGIN
PRINT @COUNT
EXEC dbo.proc_test_table2
END
ELSE
BEGIN
PRINT @COUNT
PRINT 'NO'
END
GO
insert test_table2 (number,value)
select 1,'onetest'
GO
insert test_table2 (number,value)
select 2,'twotest'
GO
insert test_table2 (number,value)
select 3,'threetest'
GO
June 3, 2009 at 7:38 am
Just a stupid question, are you saying that the procedure is not executing (or raising some errors) or the ELSE part is not executing? I am asking you this 'cause I don't see any reason why such a code is not working. And in fact, the same code works fine on my system.
--Ramesh
June 3, 2009 at 8:28 am
twillcomp (6/3/2009)
The insert process locks the 'second row' when it starts the insert and then the trigger fires.
The AFTER triggers fires only after the insert is completed but the data is not committed until the trigger execution completes.
twillcomp (6/3/2009)
In the trigger it is possible to look at the psuedo inserted tables or the base tables but, as soon as you set off another procedure to do the email I don't think you can use a query that tries to access that locked row. At least the process hangs when I try it.
Yes, you were right. While executing the code in the trigger, the base table is locked, and the external processes (i.e. database mail) trying to access the locked table has to wait until the table is released.
One alternative I could think of is creating a job that would check at every x min(s)/sec(s) and do the appropriate task.
Another alternative, which I would not recommend or suggest strongly to avoid it because of the consistency issues or uncommitted read of data, is to use NOLOCK query hint. To know more about the consequences of NOLOCK query hint read the following articles
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx
http://www.sqlmag.com/Article/ArticleID/92888/sql_server_92888.html
--Ramesh
June 4, 2009 at 5:49 am
Thanks to all,
Just gave a quick check, you were right in telling that the trigger holds a lock on the base table and since the after the trigger has completed its work the lock is not released and the commit on the table does not happen. So there is a deadlock situation. There is no point in giving the select statement with nolock option in the Stored procedure as the triggers holds the lock on the job and not the select query in the Procedure(no query outside the trigger will work). To test this i gave comitt in the trigger before executing the procedure it worked fine but with warnings.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Vinayak
-- Create date: 21/04/2009
-- Description:Sample Trigger
-- =============================================
ALTER TRIGGER [test_table2_trigger1]
ON [dbo].[Test_Table2]
FOR INSERT
AS
DECLARE @COUNT INT
select @count = count(*) from test_table2 nolock
IF @COUNT=2
BEGIN
print @COUNT
commit -- was just added to check
EXEC PROC_TEST_TABLE2
END
ELSE
BEGIN
PRINT 'NO'
END
Print 'sucess'
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
-- Stored Procedure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Vinayak
-- Create date: 21/04/2009
-- Description:Send mail
-- =============================================
ALTER PROCEDURE [dbo].[proc_test_table2]
-- Add the parameters for the stored procedure here
AS
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients=N'someone@someone.com',
@body='Test Mail',
@subject = 'SQL Server Trigger Mail',
@query='select * from [testdatabase].[dbo].[test_table2]',
@profile_name = 'DBMailProfile'
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/*2
Mail queued.
sucess
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.*/
was the output i got.
But the Code worked as expected.
I did this excersise as to over come a job which runs at specific interval of time. Is there any workaround for this problem as i cannot have a job as well as the nolock works on the select query in the store procedure.
June 4, 2009 at 7:05 am
You could just add a parameter to the proc_test_table2 and then in the trigger gather the information you want to report to the email message and pass it into the procedure.
CREATE PROCEDURE [dbo].[proc_test_table2] ( @param1 varchar(max) = null)
AS
BEGIN
declare @message varchar(max)
select @message = rtrim(@param1)
EXEC msdb.dbo.sp_send_dbmail @recipients=N'blah@blah',
@body=@message,
@subject = 'SQL Server Trigger Mail',
@profile_name = 'blah'
END
CREATE TRIGGER [test_table2_trigger1] ON [dbo].[Test_Table2]
AFTER INSERT
AS
DECLARE @COUNT INT,@message varchar(max)
SELECT @COUNT = count(*) FROM test_table2
PRINT convert(varchar,getdate(),121)+' |INFO| this execution inserted '+cast(@COUNT as varchar)+' rows'
IF @COUNT = 2
BEGIN
select @message = 'Inserted: '+ cast(number as varchar(10)) + ' and: ' + value
from inserted
EXEC dbo.proc_test_table2 @message
END
ELSE
BEGIN
PRINT @COUNT
PRINT 'NO'
END
GO
Not sure about the varchar(max) but you get the idea and you can make the parameter a more realistic data type and size.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply