Re: how to assign the contents of a field to a variable
From: Robin Boyd (none_at_nowhere.com)
Date: 07/14/04
- Next message: Robin Boyd: "Re: how to assign the contents of a field to a variable"
- Previous message: Moe Sizlak: "Help with orderby clause"
- In reply to: Tom Moreau: "Re: how to assign the contents of a field to a variable"
- Next in thread: Aaron [SQL Server MVP]: "Re: how to assign the contents of a field to a variable"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 14 Jul 2004 08:52:47 +0200
Hi Tom,
I think you are right about the performance issue. To be honest Performance
is not yet an issue, I wanted a solution that works, then I was going to
address performance. The idea of a staging table had occured to me too, it
has serveral advantages over the what I am trying to do here, but it will
still leave me the same problem (maybe in a different location), I will stil
have to loop through all the fields in a given table at some point, and copy
the contents to either a variable or a specific field in the staging table?
Am i missing something here ?
thanks again,
Robin
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:%23c9gHWPaEHA.716@TK2MSFTNGP11.phx.gbl...
> 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
> >
> >
>
>
- Next message: Robin Boyd: "Re: how to assign the contents of a field to a variable"
- Previous message: Moe Sizlak: "Help with orderby clause"
- In reply to: Tom Moreau: "Re: how to assign the contents of a field to a variable"
- Next in thread: Aaron [SQL Server MVP]: "Re: how to assign the contents of a field to a variable"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
