pears.SalesBrand [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.SalesBrand ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **SalesBrandID** | char(20) | NOT NULL |  |  |
| DivisionID | char(20) | NULL |  |  |
| InvoicePrefix | char(10) | NULL |  |  |
| Description | char(50) | NOT NULL |  |  |
| Analysis | char(50) | NULL |  |  |
| AlternativeInvoiceAddressEmailID | char(20) | NULL |  | may not be used |
| InvoiceEmail | char(250) | NULL |  | may not be used |
| InvoiceAddress | long varchar | NULL |  | may not be used |
| Defunct | tinyint | NOT NULL | 0 |  |

===== Primary Key =====
  * SalesBrandID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| Division | DivisionID | [[database:tables:pears_division|pears.Division (divisionid)]] | ON DELETE CASCADE |
| AlternativeInvoiceAddressEmail | AlternativeInvoiceAddressEmailID | [[database:tables:pears_alternativeinvoiceaddressemail|pears.AlternativeInvoiceAddressEmail (AlternativeInvoiceAddressEmailID)]] | ON DELETE CASCADE |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_accountoverrideinvoiceaddress|pears.AccountOverrideInvoiceAddress]] | SalesBrand | SalesBrandID | SalesBrandID |
| [[database:tables:pears_placement|pears.Placement]] | SalesBrand | SalesBrandID | SalesBrandID |

===== Indexes =====
  * No indexes found.

===== Triggers =====
^ Name ^ Timing ^ Event ^
| SalesBrandAudit | after | update of "InvoiceEmail", "InvoiceAddress","InvoicePrefix" order 1 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."SalesBrand"
-- Table comment: 
-- Statement count: 8

CREATE TABLE "pears"."SalesBrand" (
    "SalesBrandID"                   char(20) NOT NULL
   ,"DivisionID"                     char(20) NULL
   ,"InvoicePrefix"                  char(10) NULL
   ,"Description"                    char(50) NOT NULL
   ,"Analysis"                       char(50) NULL
   ,"AlternativeInvoiceAddressEmailID" char(20) NULL
   ,"InvoiceEmail"                   char(250) NULL
   ,"InvoiceAddress"                 long varchar NULL
   ,"Defunct"                        tinyint NOT NULL DEFAULT 0
   ,PRIMARY KEY ("SalesBrandID" ASC) 
)
go


COMMENT ON COLUMN "pears"."SalesBrand"."AlternativeInvoiceAddressEmailID" IS 
	'may not be used'
go


COMMENT ON COLUMN "pears"."SalesBrand"."InvoiceEmail" IS 
	'may not be used'
go


COMMENT ON COLUMN "pears"."SalesBrand"."InvoiceAddress" IS 
	'may not be used'
go


ALTER TABLE "pears"."SalesBrand"
    ADD FOREIGN KEY "Division" ("DivisionID" ASC)
    REFERENCES "pears"."Division" ("divisionid")
    ON DELETE CASCADE
go


ALTER TABLE "pears"."SalesBrand"
    ADD FOREIGN KEY "AlternativeInvoiceAddressEmail" ("AlternativeInvoiceAddressEmailID" ASC)
    REFERENCES "pears"."AlternativeInvoiceAddressEmail" ("AlternativeInvoiceAddressEmailID")
    ON DELETE CASCADE
go


create trigger "SalesBrandAudit" after update of "InvoiceEmail",
"InvoiceAddress","InvoicePrefix" order 1 on
"pears"."SalesBrand"
referencing old as "old_brand" new as "new_brand"
for each row
begin
  if update("InvoiceEmail") then
    call "AuditLog"('SALESBRAND',"new_brand"."SalesBrandID","string"("new_brand"."description",' - Email'),"old_brand"."InvoiceEmail","new_brand"."InvoiceEmail")
  else if update("InvoiceAddress") then
      call "AuditLog"('SALESBRAND',"new_brand"."SalesBrandID","string"("new_brand"."description",' - Address'),"old_brand"."InvoiceAddress","new_brand"."InvoiceAddress")
    else if update("InvoicePrefix") then
        call "AuditLog"('SALESBRAND',"new_brand"."SalesBrandID","string"("new_brand"."description",' - Prefix'),"old_brand"."InvoicePrefix","new_brand"."InvoicePrefix")
      end if end if end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."SalesBrand"."SalesBrandAudit" IS 
{create trigger SalesBrandAudit 
 after update of InvoiceEmail,InvoiceAddress,InvoicePrefix  order 1 on
SalesBrand
referencing old as old_brand new as new_brand
for each row
begin
   if update  (InvoiceEmail) then   
    call AuditLog('SALESBRAND',new_brand.SalesBrandID,string(new_brand.description ,' - Email'),old_brand.InvoiceEmail,new_brand.InvoiceEmail)
   else if update  (InvoiceAddress) then 
    call AuditLog('SALESBRAND',new_brand.SalesBrandID,string(new_brand.description ,' - Address'),old_brand.InvoiceAddress,new_brand.InvoiceAddress)
   else if update  (InvoicePrefix) then  
    call AuditLog('SALESBRAND',new_brand.SalesBrandID,string(new_brand.description ,' - Prefix'),old_brand.InvoicePrefix,new_brand.InvoicePrefix)
   end if end if end if;
end
}
go
</code>