Putting aside the fact that I can't change the trigger, how do I even know
if the update updated or not? I need something like this:
SET NOCOUNT ON
-- Set it up so that you can see if the update actually occurred
DECLARE @Audit_ID
SELECT MAX(IDENTITYCOL) AS @Audit_ID
FROM Time_Actual_Audit
-- Attempt to set the clockin
UPDATE Time_Actual
SET Approved_Payroll = 0
WHERE Time_Actual_ID = 34447
-- Check to see id our audit table has a new record in it
IF @Audit_ID = SELECT MAX(IDENTITYCOL) FROM Time_Actual_Audit
BEGIN
UPDATE Time_Actual_Audit
-- The user here is the approver
SET Users_ID = 1427
WHERE (Time_Actual_Audit_ID = (SELECT MAX(IDENTITYCOL) FROM
Time_Actual_Audit))
END
SET NOCOUNT OFF
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 26, 2001 4:34 PM
To: SQL
Subject: Re: How do I Access the Output of a Trigger From A Stored
Procedure?
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
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
