UDF's / sprocs inside triggers
-
Hi,
I'd like to use a udf or a sproc to reduce repetition in a trigger.
<code>
IF EXISTS(SELECT 1 FROM INSERTED
WHERE tblClientId = (SUBSELECT))
<\code>
...no problems
HOWEVER, If i substitute the SUBSELECT for a UDF/sproc that contains exactly the same syntax and return values, I get an error.
I have prefixed the udf/sproc with the name of the database (as I understand that triggers reside in the "master" database)
still no joy.
do I have to keep repeating sub selects in my triggers?
ta,
yogi
-
Are you prefixing your UDF with dbo. ?
-
hi,
yes, it errors:
"the column prefix 'dbo' does not match with a table name or alias used in the query"
ta, yogi
-
I've never used a UDF inside a trigger, but I used SPs inside triggers all the time. Never had a problem.
What's your subselect, and what's the error?
-
Hi,
SUBSELECT
<code>
IF EXISTS(SELECT 1 FROM INSERTED
WHERE jobTypeId =
(SELECT jobTypeId
FROM tblJob
WHERE jobType = 'cleanUp'))
<\code>
sproc
<code>
IF EXISTS(SELECT 1 FROM INSERTED
WHERE jobTypeId =
(dbo.spJobTypeIdPerJobName('cleanUp'))
<\code>
cheers,
yogi
-
Store proc cannot reference to INSERTED table at Trigger. INSERTED table will not be identified outside of the scope of the trigger itself.
One of the methods is using Temporary Table. Store procedure can access Temporary Table created in the same session. Here is the example:
/* TRIGGER */
CREATE TRIGGER T_B4_INSERT
INSTEAD OF INSERT
AS
SELECT * INTO #TMPTBL FROM INSERTED
EXEC DBO.EXECATSTOREPROC
/* STORE PROC */
CREATE PROCEDURE DBO.EXECATSTOREPROC
BEGIN
IF EXISTS(SELECT 1 FROM #TMPTBL
WHERE jobTypeId =
(SELECT jobTypeId
FROM tblJob
WHERE jobType = 'cleanUp'))
PRINT 'HELLO WORLD'
END
*The syntacs of above codes will be incorrect as the author do not test it out in the real environment. User(s) may need to do some modification to the codes before it work!
Regards,
kokyan
Regards,
kokyan 
-
Thanks kokyan,
I'll try that..
yogi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply