Update Trigger Calling SPROC - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Update Trigger Calling SPROC

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-04-23 : 16:12:52

I have the following update trigger that calls a SPROC to update another table database in another database.

************************************************
CREATE TRIGGER [DB2_Update] ON [dbo].[TrackData]
AFTER UPDATE
AS

DECLARE @TD_Key int
SELECT @TD_Key = ins.TrackDataIDKey
FROM inserted ins

PRINT @TD_Key

EXEC DB2_TDUpdate @TD_Key
*************************************
So, if I do:

UPDATE TrackData SET Cleared_By = 'TrigTest' WHERE DB2_Key IS NOT NULL

It only calls the SPROC for one record. How come it's not calling it for all 18 records that were updated? I know this because it only prints one @TD_Key.

TIA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-23 : 16:15:55

Because of:

SELECT @TD_Key = ins.TrackDataIDKey
FROM inserted ins

You can only store one value in a variable at a time. The inserted table has 18 rows in your case but you are only putting one of those values into a variable. It does not loop for you. You'll either need to loop through the inserted table or rewrite DB2_TDUpdate so that it can handle more than one TrackDataIDKey.

Tara

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-04-23 : 16:17:51

OK, how can I 'loop' through the Inserted Table, calling the SPROC for each key like above?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-23 : 16:20:33

You'll need to use WHILE.

I would strongly recommend against looping in a trigger. When someone does the UPDATE, the UPDATE does not complete until the trigger is completely done. If you add looping to your trigger, it is going to be very slow. I recommend rethinking this. Do you really need to do this in a trigger? Can you modify DB2_TDUpdate?

Tara

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-04-23 : 16:34:26

Well, the functionality of the SPROC's called from the triggers could change from client to client. It won't be up to me in the long run. I am using this sproc as a "hook" for inserts and updates from the TrackData table into any other table that the user decides on. So I originally called the sproc from the app front-end whenever I did an insert (DB2_TDInsert sproc) or update (DB2_TDUpdate Spoc), but thought using SQL triggers would be better that that.

Can I use the inserted table at the sproc level?
Here's what the DB2_Update SPROC does. It basically populates a set of variables from the record of the primary key passed to the sproc. Then it updates another table (DB2_TrackData). This is what the client would change.

******************************************************
CREATE PROCEDURE DB2_TDUpdate
@TrackDataIDKey int /* Primary Key of changed record */
AS

/* TrackData Update Secondary Database Hook called from ERMS Web Service or another SPROC
The rest of this Sproc serves as demonstration only for updating a secondary database using the TrackDataIDKey
as passed from the ERMS Web Service whenever a record is updated in the Enovision.dbo.TrackData table
RETURN - Uncomment to disable this feature

As of 4/23/04 the only SPROC calling this SPROC is Clear_Error
*/

DECLARE @RetVal int /* Return Value */
DECLARE @RowsUpdated int /* Rows updated after UPDATE */

/* The following vars are used to store the changed TrackData Record */
DECLARE @SerialNumber char(8), @Time_Stamp datetime, @ClientCode char(8), @InputCode char(64),
@ErrorCode char(8), @Text char(128), @Cleared_By char(20), @Cleared_On datetime, @ErrorLevel smallint,
@ERMSDate datetime, @DB2_Key int

/* Retrieve Record from Enovision TrackData */

SELECT @SerialNumber = SerialNumber, @Time_Stamp = Time_Stamp, @ClientCode = ClientCode,
@InputCode = InputCode, @ErrorCode = ErrorCode, @Text = Text, @Cleared_By = Cleared_By,
@Cleared_On = Cleared_On, @ErrorLevel = ErrorLevel, @ERMSDate = ERMSDate,
@DB2_Key = DB2_Key
FROM TrackData WHERE TrackDataIDKey = @TrackDataIDKey

IF @@ROWCOUNT = 1
BEGIN
/* Update data record into DB2 from Enovision.TrackData values */
UPDATE DB2_Sample.dbo.DB2_TrackData
SET
DB2_SerialNumber = @SerialNumber,
DB2_Time_Stamp = @Time_Stamp,
DB2_ClientCode = @ClientCode,
DB2_InputCode = @InputCode,
DB2_ErrorCode = @ErrorCode,
DB2_Text = @Text,
DB2_Cleared_By = @Cleared_By,
DB2_Cleared_On = @Cleared_On,
DB2_ErrorLevel = @ErrorLevel,
DB2_ERMSDate = @ERMSDate,
DB2_TrackDataKey = @TrackDataIDKey
WHERE
DB2_IDKey = @DB2_Key

/* If successfull then return @@ROWCOUNT
* If not succesful, then return error code * -1
*/

SELECT @RetVal = @@ERROR, @RowsUpdated = @@ROWCOUNT
END
ELSE
BEGIN
SET @RetVal = -1001 /* Select from TrackData failed */
SET @RowsUpdated = 0
END

IF (@RetVal = 0 and @RowsUpdated > 0)
Set @RetVal = @RowsUpdated
ELSE
BEGIN /* Record Error for Diagnostics */
INSERT INTO SysError (IDKey,IDKey2,RetVal,Msg) VALUES (@TrackDataIDKey,@DB2_Key,@RetVal,'DB2UPD')
END

GO
Thanks Tara.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-23 : 16:40:59

No you can not use the inserted table in a stored procedure. The inserted table only exists in the trigger.

I would duplicate the stored procedure logic in the trigger. I would change the UPDATE statement so that it uses a JOIN to the inserted table. I would do the same for the INSERT. It doesn't appear you need to use variables. I could be wrong though. Here's an example UPDATE joined to the inserted table:

UPDATE t1
SET Column1 = i.ID, Column2 = i.ColumnA
FROM Table1 t1
INNER JOIN inserted i
ON t1.ID = i.ID

Tara

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-04-23 : 16:47:21

The only reason I was using the variables was for clarity to another user. But the hell with'em! I will look into this tonight & post the results so you can tell me if I'm on the right track. I had a feeling I would be turning the SPROC code into the trigger! For now, it's Miller Time!

Thanks again Tara.

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-04-24 : 06:42:20

Thanks Tara - worked like a charm

CREATE TRIGGER [DB2_Update] ON [dbo].[TrackData]
AFTER UPDATE
AS
/* TrackData Update Secondary Database Hook
The rest of this trigger serves as demonstration only for updating a secondary database
*/
--RETURN /* Uncomment to disable this Trigger */

DECLARE @ErrorVal int

/* Update data records in DB2 from Enovision.TrackData values */
UPDATE t1
SET
DB2_SerialNumber = i.SerialNumber,
DB2_Time_Stamp = i.Time_Stamp,
DB2_ClientCode = i.ClientCode,
DB2_InputCode = i.InputCode,
DB2_ErrorCode = i.ErrorCode,
DB2_Text = i.Text,
DB2_Cleared_By = i.Cleared_By,
DB2_Cleared_On = i.Cleared_On,
DB2_ErrorLevel = i.ErrorLevel,
DB2_ERMSDate = i.ERMSDate,
DB2_TrackDataKey = i.TrackDataIDKey
FROM DB2_Sample.dbo.DB2_TrackData t1
INNER JOIN inserted i ON t1.DB2_IDKey = i.DB2_Key
WHERE i.DB2_Key IS NOT NULL
-- AND i.SerialNumber != 'REMTEST ' /* Do not add testing records */

Select @ErrorVal = @@ERROR

/* Record Error for Diagnostics */
IF (@ErrorVal != 0)
BEGIN
INSERT INTO SysError (IDKey,IDKey2,RetVal,Msg)
Select i.TrackDataIDKey,i.DB2_Key,@ErrorVal,'DB2UPD'
FROM inserted i
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources