Alternative method to a Trigger needed

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

From here: http://blog.sqlauthority.com/2007/10/01/sql-server-2005-output-clause-example-and-explanation-with-insert-update-delete/

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:

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

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

A trigger is not required...I don't think it will work if the table has a trigger (not tested).