March 2, 2011 at 2:56 am
Hi,
I have 3 synchronised databases (Live, Pre-Production and Test). The tables are updated periodically across all 3 databases as/when changes are needed within the associated AX application. I need to perform a task within the ‘Live’ environment on 1 of the tables but not within the other environments.
The requirement
When a record is inserted into table 'Mileage', I need to produce an xml file based on the specific column values for that record. One approach would be to:
I don’t wish to create the Trigger within the other environments and there may be a danger of Trigger replication in 'Pre-Production' if the tables/indexes are dropped/rebuilt when copying from 'Live'. Are there any other methods available within SQL that work similar to a Trigger that would allow me to perform the aforementioned process when a new record is inserted into a table without changing the 'Mileage' table structure?
Any ideas please?
Thanks in advance,
March 2, 2011 at 3:20 am
Maybe an alternative to a trigger is the OUPUT statement:
DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))
----Insert values in real table as well use OUTPUT clause to insert
----values in the temp table.
INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES (2,'SecondVal')
----Check the values in the temp table and real table
----The values in both the tables will be same
SELECT * FROM @TmpTable
SELECT * FROM TestTable
----Clean up time
DROP TABLE TestTable
March 2, 2011 at 3:44 am
Have you considered a computed column for this?
A quick example is:
DECLARE @TABLE TABLE
(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
XmlFile AS (CAST('<root><Id>' + CAST(Id AS VARCHAR(10)) + '</Id><Name>' + Name + '</Name></root>' AS XML))
)
INSERT INTO @TABLE(Name) VALUES('Bob'),('Bill');
SELECT * FROM @Table
To do this for real, replace the CAST line with a scalar function that takes in the column values as parameters, and returns XML generated by a FOR XML statement.
I'd speed test this before deploying into production, as you may get poor performance depending on complexity and number of updates/inserts.
Edit: My bad, you're putting the XML into a different table - Just ignore me :ermm:
March 2, 2011 at 5:04 am
Hi Tyson,
Thanks for the tip. The INSERT table/method is VERY useful - but wouldn't I still require a TRIGGER to run it though?
Using this method I would still need to 'pole' the table for any new record though. Or perhaps I'm missing the point!
Thanks for your assistance,
Neal
March 2, 2011 at 5:40 am
Hi Leo,
Thanks for your help. I realise that my original posting may have been mis-leading as to the xml requirement.
I need a process to transform the 'NEW' record into an xml file - NOT populating an xml datatype column in the 'Mileage_New' table. I'll investigate your code for clues.
Many thanks,
Neal
March 2, 2011 at 6:13 am
DerbyNeal (3/2/2011)
Hi Tyson,Thanks for the tip. The INSERT table/method is VERY useful - but wouldn't I still require a TRIGGER to run it though?
Using this method I would still need to 'pole' the table for any new record though. Or perhaps I'm missing the point!
Thanks for your assistance,
Neal
A trigger is not required...I don't think it will work if the table has a trigger (not tested).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply