Enabling and disabling a SQL Trigger

Steve Gray — Wed, 15 Oct 2008 17:26:00 GMT

Sometimes you might need to disable a trigger to keep it from recursing.

ALTER TABLE [yourtable}
DISABLE TRIGGER [yourtriggername]

and

ALTER TABLE [yourtable]
ENABLE TRIGGER [yourtriggername]

for XML PATH

Steve Gray — Tue, 26 Aug 2008 22:22:00 GMT

One of the coolest things to come with SQL 2005 is the new 'for XML PATH' syntax. There are a lot of things that can be done with it, I'm going to demonstrate something moderately complex here. The task is to generate an XML file that has nested nodes. In other words, we want the following structure:

<Receivable documentNumber="CREDT000000000010" documentDate="08/26/2008">
  <Type>Credit Adjustment</Type>
  <Amount>300.00000</Amount>
  <Items>
    <Item type="Statement" itemNumber="346.001">
      <AppliedAmount>55.00000</AppliedAmount>
      <UnappliedAmount>0</UnappliedAmount>
    </Item>
    <Item type="Statement" itemNumber="346.002">
      <AppliedAmount>11.00000</AppliedAmount>
      <UnappliedAmount>0</UnappliedAmount>
    </Item>
  </Items>
</Receivable>

Note the nested 'Items' node. We achieve this using a nested SQL Select statement inside the main query, like this:

 select 
   'Credit Adjustment' as Type,
   ortrxamt as Amount,
   (
    select 
      case when apply.aptodcnm is null then 'Case' else 'Statement' end as <a href="mailto:'@type'">'@type'</a>,
      rtrim(isnull(apply.aptodcnm,'')) as <a href="mailto:'@itemNumber'">'@itemNumber'</a>,
      isnull(apply.apfrmaplyamt,0) as 'AppliedAmount',
      0 as 'UnappliedAmount'
     from RM20201 apply
     where  apply.Apfrdcnm = rm.docnumbr and rm.rmdtypal = apply.apfrdcty
     for xml path('Item') , type
   ) as 'Items'
  from RM20101 rm
  where rm.dex_row_id = 306
  for xml  path('Receivable')

The 'type' clause in the nested select tells the code to output XML

SQL Server (T-SQL)

Enabling and disabling a SQL Trigger

for XML PATH