SQL Trigger with Calculation

MimUK
MimUK used Ask the Experts™
on
I have a SQL table called company that will have a row as follows

comp_companyid               121
comp_afp                            6
comp_tps                            6
comp_tpsconsumed           3
comp_afpsconsumed         2
comp_tpbillingstatus           (CHARGE or NONCHARGE)
comp_afpbilling                   (CHARGE or NON CHARGE)

I need that when the company is update
that if the comp_afp - comp_afpsconsumed < '1'
Then the comp_afpbilling = ' CHARGE' else 'NONCHARGE'

the same with
that if the comp_tps - comp_tpsconsumed < '1'
Then the comp_tpbillingstatus = ' CHARGE' else 'NONCHARGE'

Many thanks

Mim
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Try this:

CREATE TRIGGER trig_company
ON company
FOR UPDATE
AS
DECLARE @Check INT
SELECT @Check = (SELECT comp_afp - comp_afpsconsumed FROM Inserted)
IF(@Check < 1)
BEGIN
  SET NEW.comp_afpbilling = 'CHARGE'
ELSE
  SET NEW.comp_afpbilling = 'NON-CHARGE'
END

Commented:
Sorry, I believe this is what you need:

CREATE TRIGGER trig_company
ON company
FOR UPDATE
AS
DECLARE @Check INT
BEGIN
  SET @Check = (SELECT comp_afp - comp_afpsconsumed FROM Inserted)
  IF(@Check < 1)
    SET NEW.comp_afpbilling = 'CHARGE'
  ELSE
    SET NEW.comp_afpbilling = 'NON-CHARGE'
END

Author

Commented:
I get
Incorrect syntax near '.'.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

If this is Sage CRM triggers are not supported but here you go...

Lee
create trigger SetBilling
on company
for update
not for replication
as
begin
    declare @iCompanyId int,
            @iAFP int,
            @iAFPConsumed int
 
    select @iCompanyId = comp_companyid,
           @iAFP = isnull(comp_afp, 0),
           @iAFPConsumed = isnull(comp_afpconsumed, 0)
      from updated
 
    if (@iAFP - @iAFPConsumed) < 1
        update company set comp_afpbilling = 'CHARGE'
    else
        update company set comp_afpbilling = 'NONCHARGE'
end

Open in new window

Author

Commented:
On LIne 9

    SET NEW.comp_afpbilling = 'CHARGE'

Author

Commented:
Hi Lee,
Yes it is Sage CRM.
The integers are allocated during the opportunity workflow.
You are right, I may rethink and do the update as a tablescript or another sql workflow action.

Your script is giving errors in CRM (SQL 208)
the from updated

Sean
The tablescript will be simple enough.

In the UpdateRecord() function do a function call to this:

function UpdateBilling()
{
  var iAFP = Values("comp_afp");
  var iAFPConsumed = Values("comp_afpconsumed");
  var sAFPBilling;

  if(iAFP && iAFPConsumed)
  {
    if((iAFP - iAFPComsumed) < 1)
    {
      sAFPBilling = "CHARGE";
    }
    else
    {
      sAFPBilling = "NONCHARGE";
    }

    Values("comp_afpbilling") = sAFPBilling;
  }
}

Something like that. It isn't tested obviously but the principle is there. This question probably should be moved to the CRM zone.

Lee
HI, TRY THE BELOW QUERY. ASSUMING I HAVE DECLARED THE VARIABLE WITH VARCHAR.

CREATE TABLE COMPANY(comp_companyid VARCHAR(121),
comp_afp VARCHAR(6),
comp_tps VARCHAR(6),
comp_tpsconsumed VARCHAR(3),
comp_afpsconsumed VARCHAR(2),
comp_tpbillingstatus VARCHAR(10),  
comp_afpbilling VARCHAR(10))      

CREATE TRIGGER trigg_COMPANY ON COMPANY
FOR INSERT,UPDATE
AS BEGIN
      DECLARE @C_afp varchar(6),@C_tps Varchar(6),@C_tpsconsumed Varchar(3),@C_afpsconsumed Varchar(2)
      SET      @C_afp=(SELECT comp_afp FROM INSERTED)
      SET @C_tps=(SELECT comp_tps FROM INSERTED)
      SET @C_tpsconsumed=(SELECT comp_tpsconsumed FROM INSERTED)
      SET @C_afpsconsumed=(SELECT comp_afpsconsumed FROM INSERTED)

      -- FIRST CONDITION
      --I need that when the company is update
      --      that if the comp_afp - comp_afpsconsumed < '1'
      --Then the comp_afpbilling = ' CHARGE' else 'NONCHARGE'
      IF ISNULL(CONVERT(INT,@C_afp),0)-ISNULL(CONVERT(INT,@C_afpsconsumed),0)<1
            BEGIN
                  UPDATE COMPANY SET comp_afpbilling='CHARGE'
                  FROM COMPANY A,INSERTED B
                  WHERE A.comp_companyid=B.comp_companyid
            END
      ELSE
            BEGIN
                  UPDATE COMPANY SET comp_afpbilling='NONCHARGE'
                  FROM COMPANY A,INSERTED B
                  WHERE A.comp_companyid=B.comp_companyid
            END

      --SECOND CONDITION
      --the same with
      --that if the comp_tps - comp_tpsconsumed < '1'
      --Then the comp_tpbillingstatus = ' CHARGE' else 'NONCHARGE'
      IF ISNULL(CONVERT(INT,@C_tps),0)-ISNULL(CONVERT(INT,@C_tpsconsumed),0)<1
            BEGIN
                  UPDATE COMPANY SET comp_tpbillingstatus='CHARGE'
                  FROM COMPANY A,INSERTED B
                  WHERE A.comp_companyid=B.comp_companyid
            END
      ELSE
            BEGIN
                  UPDATE COMPANY SET comp_tpbillingstatus='NONCHARGE'
                  FROM COMPANY A,INSERTED B
                  WHERE A.comp_companyid=B.comp_companyid
            END
END                              

INSERT INTO COMPANY
SELECT 'ABCD','10','10','5','5','NON','NON'

Author

Commented:
Thanks You Very Much

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial