February 20, 2012 at 4:44 pm
I have a trigger and I want to kick it off from a stored procedure. I am using ms access and when i run the trigger from ms access it gives me an error msg (ODBC). I think I can't create triggers using ms access. This is my trigger:
IF EXISTS
(SELECT name
FROM sys.objects
WHERE name = 'UpdateComments' AND type = 'TR') DROP TRIGGER tblEmailHdr_abenit01.UpdateComments;
GO
CREATE TRIGGER UpdateComments
ON tblEmailHdr_abenit01
AFTER Update
AS
IF ( UPDATE (Comments) ) BEGIN Update ttblEmailHdr_abenit01
Set UpdateComm = GetDate()
END;
GO
This is how I have been trying to create the trigger from the stored procedure but I get the following error msg's when I try to create the sproc:
Sproc:
CREATE PROCEDURE dbo.SP_AS_tblEmailHdr_Trig (@UserID as varchar(10))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
-- Insert statements for procedure here
Declare @UserTable Varchar(50)
Declare @UserTable2 Varchar(50)
Set @UserTable = 'tblEmailHdr_' + @user-id ;
Set @UserTable2 = 'tblEmailHdr_' + @user-id + '.UpdateComments' ;
IF EXISTS
(SELECT name
FROM sys.objects
WHERE name = 'UpdateComments' AND type = 'TR') DROP TRIGGER @UserTable2
GO
CREATE TRIGGER UpdateComments
ON @UserTable
AFTER UPDATE
AS
IF ( UPDATE (Comments) )
BEGIN
--RAISERROR (50009, 16, 10)
Update @UserTable
Set UpdatedComm = GetDate()
END
GO
END
GO
error msg i get:
Msg 102, Level 15, State 1, Procedure SP_AS_tblEmailHdr_Trig, Line 23
Incorrect syntax near '@UserTable2'.
Msg 102, Level 15, State 1, Procedure UpdateComments, Line 2
Incorrect syntax near '@UserTable'.
Msg 1087, Level 15, State 2, Procedure UpdateComments, Line 8
Must declare the table variable "@UserTable".
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'END'.
February 20, 2012 at 6:18 pm
sorry...i edited my original msg as it was not clearly laid out...
just in case..here it is again:
I have a trigger and I want to kick it off from a stored procedure. I am using ms access and when i run the trigger from ms access it gives me an error msg (ODBC). I think I can't create triggers using ms access. This is my trigger:
IF EXISTS
(SELECT name
FROM sys.objects
WHERE name = 'UpdateComments' AND type = 'TR') DROP TRIGGER tblEmailHdr_abenit01.UpdateComments;
GO
CREATE TRIGGER UpdateComments
ON tblEmailHdr_abenit01
AFTER Update
AS
IF ( UPDATE (Comments) ) BEGIN Update ttblEmailHdr_abenit01
Set UpdateComm = GetDate()
END;
GO
This is how I have been trying to create the trigger from the stored procedure but I get the following error msg's when I try to create the sproc:
Sproc:
CREATE PROCEDURE dbo.SP_AS_tblEmailHdr_Trig (@UserID as varchar(10))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
-- Insert statements for procedure here
Declare @UserTable Varchar(50)
Declare @UserTable2 Varchar(50)
Set @UserTable = 'tblEmailHdr_' + @user-id ;
Set @UserTable2 = 'tblEmailHdr_' + @user-id + '.UpdateComments' ;
IF EXISTS
(SELECT name
FROM sys.objects
WHERE name = 'UpdateComments' AND type = 'TR') DROP TRIGGER @UserTable2
GO
CREATE TRIGGER UpdateComments
ON @UserTable
AFTER UPDATE
AS
IF ( UPDATE (Comments) )
BEGIN
--RAISERROR (50009, 16, 10)
Update @UserTable
Set UpdatedComm = GetDate()
END
GO
END
GO
error msg i get:
Msg 102, Level 15, State 1, Procedure SP_AS_tblEmailHdr_Trig, Line 23
Incorrect syntax near '@UserTable2'.
Msg 102, Level 15, State 1, Procedure UpdateComments, Line 2
Incorrect syntax near '@UserTable'.
Msg 1087, Level 15, State 2, Procedure UpdateComments, Line 8
Must declare the table variable "@UserTable".
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'END'.
February 20, 2012 at 6:51 pm
With statements that do things like create triggers or tables or other data structures, (these are called DDL statements, I think), you can't just supply a variable name the way you might if you were using VBA within Access or Excel. You probably need dynamic SQL within your SPROC, so you'd create a variable to contain the entire CREATE TRIGGER statements, and then within the sproc, you'd use EXEC (@sql).
Another problem is that you can't have GO in the middle of a stored procedure. It will end the batch right there, and anything beyond it is NOT a part of the procedure. Does that help?
EDIT:
Perhaps the following:
CREATE PROCEDURE dbo.SP_AS_tblEmailHdr_Trig (@UserID as varchar(10))
AS
BEGIN
SET NOCOUNT ON
DECLARE @UserTable AS Varchar(50), @UserTable2 AS Varchar(50)
DECLARE @SQLDROP AS varchar(255), @SQLCREATE AS varchar(max)
SET @UserTable = 'tblEmailHdr_' + @user-id ;
SET @UserTable2 = 'tblEmailHdr_' + @user-id + '.UpdateComments' ;
SET @SQLDROP = 'DROP TRIGGER ' + @UserTable2
SET @SQLCREATE = 'CREATE TRIGGER UpdateComments
ON ' + @UserTable + '
AFTER UPDATE
AS
IF ( UPDATE (Comments) )
BEGIN
--RAISERROR (50009, 16, 10)
Update ' + @UserTable + '
Set UpdatedComm = GetDate()
END
GO
'
IF EXISTS
(
SELECT name
FROM sys.objects
WHERE name = 'UpdateComments'
AND type = 'TR')
BEGIN
EXEC (@SQLDROP)
EXEC (@SQLCREATE)
END
END
GO
Then in Access, you'd have a "Pass-Through" query that just executes the proc.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 20, 2012 at 8:53 pm
Thanks! I had to make a minor change and this is what worked for me. The only issue is that I linked the table to ms access and when i edit the comments field it, updates the column UpdatedComm ,but it updates the entire table with the datetime. When I edit the comments field in sql server it only updates 1 record, the record that I modified comments field.
ALTER PROCEDURE dbo.SP_AS_tblEmailHdr_Trig (@UserID as varchar(10))
AS
BEGIN
SET NOCOUNT ON
DECLARE @UserTable AS Varchar(50), @UserTable2 AS Varchar(50)
DECLARE @SQLDROP AS varchar(255), @SQLCREATE AS varchar(max)
SET @UserTable = 'tblEmailHdr_' + @user-id ;
SET @UserTable2 = 'tblEmailHdr_' + @user-id + '.UpdateComments' ;
SET @SQLDROP = 'IF EXISTS
(
SELECT name
FROM sys.objects
WHERE name = ''UpdateComments''
AND type = ''TR'')
DROP TRIGGER ' + @UserTable2
SET @SQLCREATE = 'CREATE TRIGGER UpdateComments
ON ' + @UserTable + '
AFTER UPDATE
AS
IF ( UPDATE (Comments) )
BEGIN
--RAISERROR (50009, 16, 10)
Update ' + @UserTable + '
Set UpdatedComm = GetDate()
END
'
EXEC (@SQLDROP)
EXEC (@SQLCREATE)
END
GO
February 20, 2012 at 10:11 pm
That's the nature of triggers. You need to be able to handle ANY number of records in the trigger code. Unfortunately, if you're modifying individual records within Access by using the good old grid that Access provides by double-clicking on a table, then you may be out of luck. You might be able to try modifying the UPDATE statement to add a WHERE clause along the lines of:
EDIT: had to fix the whole thing - -
CREATE PROCEDURE dbo.SP_AS_tblEmailHdr_Trig (@UserID as varchar(10))
AS
BEGIN
SET NOCOUNT ON
DECLARE @UserTable AS Varchar(50), @UserTable2 AS Varchar(50)
DECLARE @SQLDROP AS varchar(255), @SQLCREATE AS varchar(max)
SET @UserTable = 'tblEmailHdr_' + @user-id ;
SET @UserTable2 = 'tblEmailHdr_' + @user-id + '.UpdateComments' ;
SET @SQLDROP = 'DROP TRIGGER ' + @UserTable2
SET @SQLCREATE = 'CREATE TRIGGER UpdateComments
ON ' + @UserTable + '
AFTER UPDATE
AS
IF ( UPDATE (Comments) )
BEGIN
--RAISERROR (50009, 16, 10)
Update UT
Set UpdatedComm = GetDate()
FROM ' + @UserTable + ' AS UT
WHERE UT.someuniqueidentifier-- Ideally, the primary key field
IN (SELECT someuniqueidentifier
FROM (
SELECT *
FROM INSERTED
EXCEPT
SELECT *
FROM DELETED
) AS X
)
END
GO
'
IF EXISTS
(
SELECT name
FROM sys.objects
WHERE name = 'UpdateComments'
AND type = 'TR')
BEGIN
EXEC (@SQLDROP)
EXEC (@SQLCREATE)
END
END
GO
I'm not certain this will do any good, and it might drive performance into the ditch on any UPDATE if the number of records is large enough.
Please TEST cautiously. The words INSERTED and DELETED refer to "virtual" tables that exist in the context of any trigger, with the former representing inserted records or the updated records (post-update), and the latter representing any deleted records or the updated records (pre-update).
Let me know if that helps...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 21, 2012 at 7:50 am
You might want to add an IF EXISTS around both the create and drop statements. The way this is if you run it twice with the same parameter it will fail.
I can't for the life of me come up with a valid reason why you would want a stored procedure to create and drop triggers.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 21, 2012 at 8:03 am
Sean Lange (2/21/2012)
...I can't for the life of me come up with a valid reason why you would want a stored procedure to create and drop triggers.
I try:
In case of audit solution based on audit triggers (quite common huh?).
You may want to create (and drop) triggers on fly. Actually, it could be for any common auto-generated triggers, why not?
Current database I'm working on, has DEV schema which is placeholder for all kind of utility functions and procs, some of them do exactly that: generate audit triggers (and also audit tables...).
The question I would have: why would you want to call such procs from MSAccess?
February 21, 2012 at 8:05 am
I made minor fixes...but worked like a charm! Thanks!
CREATE PROCEDURE dbo.SP_AS_tblEmailHdr_Trig (@UserID as varchar(10))
AS
BEGIN
SET NOCOUNT ON
DECLARE @UserTable AS Varchar(50), @UserTable2 AS Varchar(50)
DECLARE @SQLDROP AS varchar(255), @SQLCREATE AS varchar(max)
DECLARE @TrigName as varchar(50)
SET @TrigName = 'UpdateComments' + @user-id ;
SET @UserTable = 'tblEmailHdr_' + @user-id ;
SET @UserTable2 = 'tblEmailHdr_' + @user-id + '.UpdateComments' + @user-id ;
SET @SQLDROP = 'IF EXISTS
(
SELECT name
FROM sys.objects
WHERE name = ''' + @TrigName + '''
AND type = ''TR'')
DROP TRIGGER ' + @UserTable2
SET @SQLCREATE = 'CREATE TRIGGER ' + @TrigName + '
ON ' + @UserTable + '
AFTER UPDATE
AS
IF ( UPDATE (Comments) )
BEGIN
--RAISERROR (50009, 16, 10)
Update UT
Set UpdatedComm = GetDate()
FROM ' + @UserTable + ' AS UT
WHERE UT.ID-- Ideally, the primary key field
IN (SELECT ID
FROM (
SELECT *
FROM INSERTED
EXCEPT
SELECT *
FROM DELETED
) AS X
)
END
'
--BEGIN
Print @SQLDROP
Print '----------'
Print @SQLCREATE
EXEC (@SQLDROP)
EXEC (@SQLCREATE)
--END
END
February 21, 2012 at 8:07 am
Eugene Elutin (2/21/2012)
The question I would have: why would you want to call such procs from MSAccess?
I have to admit I shudder everytime people on here talk about using Access as their front end. I gave up long ago on questioning people about it. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 21, 2012 at 8:08 am
Sean Lange (2/21/2012)
You might want to add an IF EXISTS around both the create and drop statements. The way this is if you run it twice with the same parameter it will fail.I can't for the life of me come up with a valid reason why you would want a stored procedure to create and drop triggers.
With MS Access, ODBC will not allow me to create a trigger, so that is why I am using a sproc to creat the trigger when the temp table get created.
February 21, 2012 at 8:14 am
GrassHopper (2/21/2012)
Sean Lange (2/21/2012)
You might want to add an IF EXISTS around both the create and drop statements. The way this is if you run it twice with the same parameter it will fail.I can't for the life of me come up with a valid reason why you would want a stored procedure to create and drop triggers.
With MS Access, ODBC will not allow me to create a trigger, so that is why I am using a sproc to creat the trigger when the temp table get created.
I think I see what you are doing now. You are not using temp tables but creating permanent tables for each user and they get dropped at some point during the program?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 22, 2012 at 7:26 am
That's correct.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply