----------------------------------------------------------- New Message on BDOTNET
----------------------------------------------------------- From: RamSdotnet Message 1 in Discussion Hi I am working on SQL Server 2005. I am having two tables as follows Tables Structure Emp table : ID int Name varchar(50) Dept varchar(50) details XML EmpLog tableName VARRCHAR(50) data xml modifiedby varchar(50) modifiedtime datetime I am creating a trigger EmpTrigger on Emptable. Purpose of the trigger is to log the changes on the table. -When the user inserts a new row i want to capture whole row and place the row in the log table -When a user modifies a field , want to capture the old value and place that value in the log table -when a user deletes row i want to capture whole row and place the row in the log table In the trigger i am taking all the rows from the inserted table into a cursor and looping through to perform set based operation In the update operation -->if any column value is changed, i am capturing those values and building a xml string. XML structure <Log> <ChangedColumn>ChangedData<ChangedColumn> </Log> I am inserting this xml string into log table my insert stmt looks like Insert into EmpLog values(emp,xmlstring,host_name,getdate()); The problem is with constructing xml string in the trigger 1)If i declare xmlstring as xml I cant perform add operation on it set xmlstring = xmlstring + <ChangedColumn>ChangedData<ChangedColumn> Error : '+' can not be performed on xml datatype 2) If i declare xmlstring as text/nvarchar i cant add xml datatpe to xmlstring bcoz i am having details column as xml datatype in the emp table Error : '+' can not be performed on xml datatype I dont want to change my both tables columns datatype. I wantto return xmlstring from a trigger i wantto insert that string into the log table data column Please help me Thanks in advance Ram ----------------------------------------------------------- To stop getting this e-mail, or change how often it arrives, go to your E-mail Settings. http://groups.msn.com/bdotnet/_emailsettings.msnw Need help? If you've forgotten your password, please go to Passport Member Services. http://groups.msn.com/_passportredir.msnw?ppmprop=help For other questions or feedback, go to our Contact Us page. http://groups.msn.com/contact If you do not want to receive future e-mail from this MSN group, or if you received this message by mistake, please click the "Remove" link below. On the pre-addressed e-mail message that opens, simply click "Send". Your e-mail address will be deleted from this group's mailing list. mailto:[EMAIL PROTECTED]
