Henrik Skak Pedersen wrote:
> Hi,
>
> I have a problem with varchar(8000) in my trigger. It is a big
> problem for me that the X and Y variables below are limited by 8000
> characters, how can I make them larger?
>
> I can illustrate it like this:
>
> Create database AuditTest
> Create table Audit (
> AuditId int identity,
> deletedText text,
> insertedText text
> )
>
> create table Employee (
> Id int identity,
> Name varchar(100)
> )
>
> CREATE trigger EmployeeAudit on Employee
> AFTER INSERT, UPDATE, DELETE
> AS
> DECLARE @X VARCHAR(8000)
> select @x = (SELECT * from deleted for xml auto, elements) --,
> xmlschema)
> DECLARE @Y VARCHAR(8000)
> select @Y = (SELECT * from inserted for xml auto, elements) --,
> xmlschema)
> INSERT INTO Audit
> values(@x, @y)
>
> Thanks Henrik

"Henrik Skak Pedersen" <skak@community.nospam> wrote in message
news:e2d%23EoC$FHA.740@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> I have a problem with varchar(8000) in my trigger. It is a big problem
> for me that the X and Y variables below are limited by 8000 characters,
> how can I make them larger?
>
> I can illustrate it like this:
>
> Create database AuditTest
> Create table Audit (
> AuditId int identity,
> deletedText text,
> insertedText text
> )
>
> create table Employee (
> Id int identity,
> Name varchar(100)
> )
>
> CREATE trigger EmployeeAudit on Employee
> AFTER INSERT, UPDATE, DELETE
> AS
> DECLARE @X VARCHAR(8000)
> select @x = (SELECT * from deleted for xml auto, elements) --,
> xmlschema)
> DECLARE @Y VARCHAR(8000)
> select @Y = (SELECT * from inserted for xml auto, elements) --,
> xmlschema)
> INSERT INTO Audit
> values(@x, @y)
>
> Thanks Henrik
>

"David Gugick" <david.gugick-nospam@quest.com> wrote in message
news:ORpWI%23C$FHA.516@TK2MSFTNGP15.phx.gbl...
> Henrik Skak Pedersen wrote:
>> Hi,
>>
>> I have a problem with varchar(8000) in my trigger. It is a big
>> problem for me that the X and Y variables below are limited by 8000
>> characters, how can I make them larger?
>>
>> I can illustrate it like this:
>>
>> Create database AuditTest
>> Create table Audit (
>> AuditId int identity,
>> deletedText text,
>> insertedText text
>> )
>>
>> create table Employee (
>> Id int identity,
>> Name varchar(100)
>> )
>>
>> CREATE trigger EmployeeAudit on Employee
>> AFTER INSERT, UPDATE, DELETE
>> AS
>> DECLARE @X VARCHAR(8000)
>> select @x = (SELECT * from deleted for xml auto, elements) --,
>> xmlschema)
>> DECLARE @Y VARCHAR(8000)
>> select @Y = (SELECT * from inserted for xml auto, elements) --,
>> xmlschema)
>> INSERT INTO Audit
>> values(@x, @y)
>>
>> Thanks Henrik
>
> A bigger problem is this:
>
> a.. FOR XML is not valid in subselections, whether it is in UPDATE,
> INSERT, or DELETE statements, a nested SELECT statement, or other
> statements (SELECT INTO, assignment). For example, subselects as shown in
> these examples are not supported:
> Example A
>
> SELECT *
> FROM Table1
> WHERE ......(SELECT * FROM Table2 FOR XML RAW)
> Example B
>
> DECLARE @doc nchar(3000)
> SET @doc = (SELECT * FROM Customers WHERE CustomerID = 'ALFKI' FOR XML
> RAW)See here for more information:http://www.sqlxml.org/faqs.aspx?faq=104
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

"JT" <someone@microsoft.com> wrote in message
news:uJXGaKE$FHA.740@TK2MSFTNGP12.phx.gbl...
>A VarChar will only store 8000 bytes maximum, but you could try whatever
>with data type Text.
>
> Perhaps XML is not the best way to store this type of object in the
> database. In the past, I've implemented loging triggers similar to below.
> The relational data is more compact and easier to query.
>
> create table Employee
> (
> AuditDate datetime,
> AuditType char(1),
> Id int identity,
> Name varchar(100)
> )
>
> CREATE trigger EmployeeAudit on Employee
> AFTER INSERT, UPDATE, DELETE
> AS
> insert into EmployeeAudit select getdate(), 'D', * from deleted
> insert into EmployeeAudit select getdate(), 'I', * from inserted
>
>
> "Henrik Skak Pedersen" <skak@community.nospam> wrote in message
> news:e2d%23EoC$FHA.740@TK2MSFTNGP12.phx.gbl...
>> Hi,
>>
>> I have a problem with varchar(8000) in my trigger. It is a big problem
>> for me that the X and Y variables below are limited by 8000 characters,
>> how can I make them larger?
>>
>> I can illustrate it like this:
>>
>> Create database AuditTest
>> Create table Audit (
>> AuditId int identity,
>> deletedText text,
>> insertedText text
>> )
>>
>> create table Employee (
>> Id int identity,
>> Name varchar(100)
>> )
>>
>> CREATE trigger EmployeeAudit on Employee
>> AFTER INSERT, UPDATE, DELETE
>> AS
>> DECLARE @X VARCHAR(8000)
>> select @x = (SELECT * from deleted for xml auto, elements) --,
>> xmlschema)
>> DECLARE @Y VARCHAR(8000)
>> select @Y = (SELECT * from inserted for xml auto, elements) --,
>> xmlschema)
>> INSERT INTO Audit
>> values(@x, @y)
>>
>> Thanks Henrik
>>
>
>