I try to avoid output functions in my triggers (other than RAISERROR and
RETURN) so I haven't actually tried this. However, the first thing I would
try would be something like this in the stored procedure:
SET @Audit_ID = (UPDATE Time_Actual .... WHERE Time_Actual_ID =
@ActualTimeID)
IF NOT @Audit_ID IS NULL AND @@ERROR = 0
BEGIN
Yadda, yadda, yadda
END
|----------------------------+--------------------------------------------|
|Eric A. Laney |"White hair is not a sign of wisdom, only |
|Systems Engineer |age" |
|LAN Optimization Team | |
|Verizon Data Services | |
|Voice: 813.978.4404 | Greek proverb|
|Pager: 888.985.8519 | |
|----------------------------+--------------------------------------------|
|---------+--------------------------->
| | Dave Babbitt |
| | <[EMAIL PROTECTED]|
| | g> |
| | |
| | 2001-12-26 15:52|
| | Please respond |
| | to sql |
| | |
|---------+--------------------------->
>------------------------------------------------------------------------------------------------------------------------------|
|
|
| To: SQL <[EMAIL PROTECTED]>
|
| cc:
|
| Subject: How do I Access the Output of a Trigger From A Stored Procedure?
|
>------------------------------------------------------------------------------------------------------------------------------|
Hi Guys!
I have a trigger on my Time_Actual table that goes like this:
CREATE trigger
updatetrigger_Time_Actual
On Time_Actual
FOR UPDATE
AS
-- Various audit trail stuff goes here
SELECT DISTINCT @@IDENTITY AS Audit_ID
FROM Time_Actual_Audit
How do I access Audit_ID from a stored procedure? I'm think of something
like this:
SET NOCOUNT ON
-- Attempt to set the clockin
UPDATE Time_Actual SET
-- Various fields are set
here
WHERE Time_Actual_ID = @ActualTimeID
-- rely on updatetrigger_Time_Actual
for the audit id
IF EXISTS(Audit_ID)
BEGIN
UPDATE Time_Actual_Audit
-- The user here is the
approver
SET Users_ID = @UsersID
WHERE
(Time_Actual_Audit_ID = Audit_ID)
END
SET NOCOUNT OFF
I want to update the audit trail row created by the time actual update ONLY
IF the time actual update actually happened. No, I can't redesign the
trigger so that this happens in the trigger.
Thanx
Dave
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
