Re: how to assign the contents of a field to a variable

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 07/13/04 

Date: Tue, 13 Jul 2004 12:20:56 -0400

You may want to rethink your design. Triggers are meant to be quick - doing
the absolute minimum necessary. Also, what happens if the MSMQ server is
down? All of your updates would fail. I would take the approach of putting
the updated rows into a staging table and then have a scheduled job wake up
every minute and process the items. 

-- 
   Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON   Canada
www.pinnaclepublishing.com/sql
.
"Robin Boyd" <none@nowhere.com> wrote in message
news:cd0tcv$2ep$1@ork.noris.net...
Hi Tom,
teh requirements of the trigger are to export an XML document to MSMQ
containinga list of the fields, and their values.  As the trigger is being
designed as a template for mulitple tables I am trying to write a generic
solution, this solution will then also supoort modifications to the table
structure in the future.
I have solved the XML generation and export to MSMQ, but am stuck with the
details of filling SQL variables!
here is thte complete code, at the moment I am simply testing by creating
records in the table, which is why there is no check for deletions (and
updates)  :)
thanks again!
Robin
-- Start of Code
ALTER TRIGGER spUpdateNavisionOrders
ON Orders
FOR INSERT, UPDATE, DELETE
As
DECLARE @int_msmqqueue INT
DECLARE @int_result INT
DECLARE @CurrField VARCHAR(100)
DECLARE @CurrFieldName VARCHAR(80)
DECLARE @CurrFieldValue VARCHAR(250)
DECLARE @Count INT
DECLARE @MySQL VARCHAR(100)
EXECUTE @int_result = sp_OACreate 'impSQLMSMQ.MSMSTools', @int_msmqqueue
OUT, 1
IF @int_result <> 0 GOTO ErrorHandler
EXECUTE @int_result = sp_OAMethod @int_msmqqueue, 'InitDoc', NULL
IF @int_result <> 0 GOTO ErrorHandler
EXECUTE @int_result = sp_OAMethod @int_msmqqueue, 'SetTable', NULL, 'Orders'
IF @int_result <> 0 GOTO ErrorHandler
EXECUTE @int_result = sp_OAMethod @int_msmqqueue, 'SetUpdateType', NULL,
'INSERT'
IF @int_result <> 0 GOTO ErrorHandler
SELECT @Count = Count(*) FROM Inserted
if @Count > 0
BEGIN
PRINT 'Inside the if construct'
DECLARE FieldCursor CURSOR
LOCAL
FAST_FORWARD
FOR
SELECT DISTINCT SC.name AS FieldName
FROM dbo.sysobjects SO INNER JOIN
dbo.syscolumns SC ON SC.id = SO.id
WHERE (SO.name = 'Orders')
OPEN FieldCursor
FETCH NEXT FROM FieldCursor INTO @CurrFieldName
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @MySQL = 'SELECT @CurrFieldValue = ' + @CurrFieldName + ' FROM
[Inserted]'
EXECUTE @MySQL
EXECUTE @int_result = sp_OAMethod @int_msmqqueue, 'AddField', NULL,
@CurrFieldName, @CurrFieldValue
IF @int_result <> 0 GOTO ErrorHandler
FETCH NEXT FROM FieldCursor INTO @CurrFieldName
END
CLOSE FieldCursor
DEALLOCATE FieldCursor
END
EXECUTE @int_result = sp_OAMethod @int_msmqqueue, 'Send', NULL,
'Direct=OS:imp-rob\private$\jobmq'
IF @int_result <> 0 GOTO ErrorHandler
GOTO DestroyObjects
Errorhandler:
DECLARE @vc_source varchar(53), @vc_description VARCHAR(200)
EXECUTE sp_OAGetErrorInfo @int_msmqqueue, @vc_source OUT, @vc_description
OUT, NULL, NULL
RAISERROR(@vc_description, 16, 1)
DestroyObjects:
EXECUTE @int_result = sp_OADestroy @int_msmqqueue
-- End of Code
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:%23yzjqaOaEHA.3988@tk2msftngp13.phx.gbl...
> Could you please post the actual requirements for your trigger? This code
> does not support multi-row updates and your dynamic SQL is attempting to
use
> variables from the calling code.
>
> -- 
>    Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON   Canada
> www.pinnaclepublishing.com/sql
> .
> "Robin Boyd" <none@nowhere.com> wrote in message
> news:cd0rpp$1ni$1@ork.noris.net...
> Hi all,
>
> I am trying to assing the value of a field (stored in a variable) to
another
> variable.  I have tried various approaches with no success :(
>
> here is the code I am working with...
>
> --SELECT@CurrFieldValue = CustomerID FROM[Inserted]
> SET @MySQL = 'SELECT @CurrFieldValue = ' + @CurrFieldName + ' FROM
> [Inserted]'
> EXECUTE @MySQL
>
>
> I currently get the following error message:
> The name 'SELECT @CurrFieldValue = CustomerID FROM [Inserted]' is not a
> valid identifier.
>
> The commented lien works perfectly, however as I execute this code from
> within a loop (for all the fields in the table) this is no good.
>
> Any help, pointers or such, most welcome!
>
> Thanks in advance,
> Robin Boyd
>
>

Relevant Pages