Trigger CODE HELP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger CODE HELP

Author

Topic

shaharru
Yak Posting Veteran

72 Posts

Posted - 2007-08-26 : 13:44:53

[code]CREATE TRIGGER tr_1
ON Users
FOR UPDATE OF u_paid

AS
BEGIN

..........

END[/code]

any idea what is wrong with the above code?
The users table exist and u_paid is a column in that table.

Im getting the following error:
Incorrect syntax near the keyword 'OF'.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-26 : 13:55:53

where did you get the OF word from?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

shaharru
Yak Posting Veteran

72 Posts

Posted - 2007-08-26 : 15:32:49

[code]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [dbo].[tr_u_paid]
ON [dbo].[Users] FOR UPDATE
AS
IF UPDATE(u_paid_member)
BEGIN
SET NOCOUNT ON

DECLARE @ExecStr varchar(50), @Qry nvarchar(255) , @Query nvarchar(255) ,@Parm nvarchar(255) ,@Event nvarchar(255)

CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)

SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'

INSERT INTO #inputbuffer
EXEC (@ExecStr)

SET @Qry = (SELECT EventType FROM #inputbuffer)
SET @Parm = (SELECT Parameters FROM #inputbuffer)
SET @Event = (SELECT EventInfo FROM #inputbuffer)

SET @Query = (SELECT @Qry)+(SELECT @Parm)+(SELECT @Event)

INSERT INTO tbErrors4
Values (@Query,SYSTEM_USER ,USER ,CURRENT_TIMESTAMP)

END[/code]

-This code works , but i have a problem.

-The whole purpose of the code is to get the SP that triggered the update but im not getting enough info from the result.

-i need to know the full SP infomation , input parameters in that SP , name of that SP...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-26 : 16:21:23

you can get that info from
syscomments sql server 2000
sys.sql_modules sql server 2005

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

shaharru
Yak Posting Veteran

72 Posts

Posted - 2007-08-27 : 07:59:19

thank you.

maybe you can help me out and write how to use sys.sql_modules ?

code example , please :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-27 : 08:02:43

what have you tried so far?
have you looked into Books Online = BOL = sql server help?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

shaharru
Yak Posting Veteran

72 Posts

Posted - 2007-08-27 : 08:30:06

I wrote the above code , as you can see.
I have googled "sys.sql_modules sql" but i couldn't find an example realted.
the only example that exists is the one on this website (http://vyaskn.tripod.com/tracking_sql_statements_by_triggers.htm) , which doesn't provide the needed information.

i hope you can give me an example. thanks.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-27 : 08:38:44

if you really googled it then you should've gotten this as the first page:
http://msdn2.microsoft.com/en-us/library/ms175081.aspx

this is an example:

SELECT o.[Name], o.[object_id], o.[type], m.definition
FROM sys.objects o
INNER JOIN sys.sql_modules m
ON o.object_id = m.object_id
-- WHERE o.[type] = 'p'
ORDER BY o.[Name];
GO

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

shaharru
Yak Posting Veteran

72 Posts

Posted - 2007-08-27 : 09:29:23

Thank you .

I did find that in google and i didn't understand how it can help me.
The truth is that even after your example ,i still cant understand how to get the information about which SP caused a trigger to fire and how do i get the addtional paramters.

I tried to run the code you wrote , it seems to list all of the SP defined in my DB.

plz help .
thank you for your time.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-27 : 09:43:56

Cleaner code

ALTER TRIGGER dbo.tr_u_paid ON dbo.Users
FOR UPDATE
AS 

IF UPDATE(u_paid_member)
	BEGIN
		SET NOCOUNT ON
 
		CREATE TABLE	#InputBuffer
				(
					EventType NVARCHAR(30), 
					Parameters INT, 
					EventInfo NVARCHAR(255)
				)

		INSERT	#InputBuffer
		EXEC ('DBCC INPUTBUFFER(' + STR(@@SPID) + ')')
 
		INSERT	tbErrors4
		SELECT	EventType + ' ' + CONVERT(NVARCHAR, Parameters) + ' ' + EventInfo,
			SYSTEM_USER,
			USER,
			CURRENT_TIMESTAMP
		FROM	#Inputbuffer
	END

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-27 : 09:47:06

If all you need is the query that updated u_paid_member, use this for faster trigger

ALTER TRIGGER dbo.tr_u_paid ON dbo.Users
FOR UPDATE
AS 

IF UPDATE(u_paid_member)
	BEGIN
		SET NOCOUNT ON
 
		DECLARE @Handle BINARY(20)

		SELECT	@Handle = Sql_Handle
		FROM	master..sysprocesses
		WHERE	SpId = @@SPID

		INSERT	tbErrors4
		SELECT	[Text],
			SYSTEM_USER,
			USER,
			CURRENT_TIMESTAMP
		FROM	::fn_get_sql(@Handle)
	END

E 12°55'05.25"
N 56°04'39.16"

shaharru
Yak Posting Veteran

72 Posts

Posted - 2007-08-27 : 09:58:53

thank you peso , the code you posted is similar to my first posted code.
the problem is that the result data is really incomplete:

Here is a line from tbErrors4 after the trigger has fired:
RPC Event 0 DatingSite.dbo.UpdateUser;1

From this i know that the SP that fired the trigger was UpdateUser ,
BUT I want to know the addtional paramters ,meaning the full code of the SP , which paramters where passed to the SP and what where there values.
something like this:
[dbo].[UpdateUser] @UserID = 5,@IsPaid = 1

thanks

shaharru
Yak Posting Veteran

72 Posts

Posted - 2007-08-27 : 10:02:54

my last message was related to the previous post you did.
you posted while i wrote my message:) , ill try the new code you wrote now.

thanks

shaharru
Yak Posting Veteran

72 Posts

Posted - 2007-08-27 : 10:13:12

I tried the new code.
The output of the code (the data written into tbErrors4) is a copy of the Trigger SP itself.

Here is the output:


CREATE TRIGGER dbo.tr_u_paid ON dbo.Users
FOR UPDATE
AS 

IF UPDATE(u_paid_member)
	BEGIN
		SET NOCOUNT ON
 
		DECLARE @Handle BINARY(20)

		SELECT	@Handle = Sql_Handle
		FROM	master..sysprocesses
		WHERE	SpId = @@SPID

		INSERT	tbErrors4
		SELECT	[Text],
			SYSTEM_USER,
			USER,
			CURRENT_TIMESTAMP
		FROM	::fn_get_sql(@Handle)
	END

shaharru
Yak Posting Veteran

72 Posts

Posted - 2007-08-28 : 08:29:08

anyone?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 08:48:09

Use the INPUT BUFFER method

ALTER TRIGGER dbo.tr_u_paid ON dbo.Users
FOR UPDATE
AS 

IF UPDATE(u_paid_member)
	BEGIN
		SET NOCOUNT ON
 
		CREATE TABLE	#InputBuffer
				(
					EventType NVARCHAR(30), 
					Parameters INT, 
					EventInfo NVARCHAR(255)
				)

		INSERT	#InputBuffer
		EXEC ('DBCC INPUTBUFFER(' + STR(@@SPID) + ')')
 
		INSERT	tbErrors4
		SELECT	EventType + ' ' + CONVERT(NVARCHAR, Parameters) + ' ' + EventInfo,
			SYSTEM_USER,
			USER,
			CURRENT_TIMESTAMP
		FROM	#Inputbuffer
	END

E 12°55'05.25"
N 56°04'39.16"

shaharru
Yak Posting Veteran

72 Posts

Posted - 2007-08-30 : 08:05:43

this is what i posted in my original message.
it doesn't work.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 08:32:22

Yes it will work. I have tried it myself right now.
The tbErrors4 is filled with records when updating the u_paid_member column in the tr_u_paid table, with statements like this

Language Event 0 update tr_u_paid set u_paid_member = u_paid_member + 1

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 08:33:42

And if a stored procedure did the update, why are you interested in how the code looked like?
What if the current update was in a nested SP situation?
SP1 calling SP2 calling SP3 which did the update.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources