Simple trigger (REALLY EASY)

hi,
i need a trigger that after a record is enter to the table does this:
SELECT
CASE WHEN CXL='1' and COMM <>0 then COMM*-1 else COMM end as COMM,
CASE WHEN CXL ='1' and GROSS_CREDIT <>0 then GROSS_CREDIT*-1 else GROSS_CREDIT end as GC
FROM BKPG
the key on this table is RECORDID
LVL 1
jsctechyAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
slight trigger modif:

create trigger trg_update_comm_and_gross_credit
ON bkpg
FOR UPDATE, INSERT
AS
  IF @@NESTLEVEL = 1
  UPDATE bkpg
     set comm = CASE WHEN i.CXL='1' and i.COMM >0 then i.COMM*-1 else i.COMM end
     , gross_credit = CASE WHEN i.CXL ='1' and i.GROSS_CREDIT >0 then i.GROSS_CREDIT*-1 else i.GROSS_CREDIT end
 FROM bkpg t
 JOIN inserted i
   ON i.recordid = t.recordid


slight insert modif: the COMM field is numeric/decimal, I assume. hence, remove the quotes:

INSERT INTO BKPG
           ([ACCT_P]
          ,[COMM]
          ,[CXL]
)
     VALUES
           ('js300000'
           ,100
           ,'1'
)

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
create trigger trg_update_comm_and_gross_credit
ON bkpg
FOR INSERT
AS
  UPDATE bkpg
     set comm = CASE WHEN i.CXL='1' and i.COMM<>0 then i.COMM*-1 else i.COMM end
     , gross_credit = CASE WHEN i.CXL ='1' and i.GROSS_CREDIT <>0 then i.GROSS_CREDIT*-1 else i.GROSS_CREDIT end
 FROM bkpg t
 JOIN inserted i
   ON i.recordid = t.recordid
0
 
jsctechyAuthor Commented:
well, that did not work.
once a new record get input it it does not do what it is supposed to do.
any other ideas?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>any other ideas?
well, maybe I misunderstood what has to be done, actually.
could you post the insert statement you use, and the resulting data you expect to be stored in the table ?
0
 
jsctechyAuthor Commented:
actually the store process is done by a batch process it does insert data as a batch.
using ADODB
0
 
jsctechyAuthor Commented:
i just test this triger on the same table and it works:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER trigger [trUpdateBKPG] on [dbo].[BKPG] for update as

UPDATE BKPG
SET DEPT_P = CASE LEFT (REP_P,1)
   WHEN 'Y' THEN  'Y'  
   WHEN 'J' THEN  'J'  
   WHEN 'L' THEN 'L'                                          
   WHEN 'N' THEN 'Y'
   WHEN '0' THEN '0'  
   ELSE   'Y'              
 END
WHERE RECORDID = (select RECORDID from Inserted)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
your trigger has been coded as
>for update

while, mine has been coded "for insert"
if you say that yours works, then the action is a UPDATE, and not a INSERT, which trigger your trigger...

please clarify

0
 
jsctechyAuthor Commented:
i need as an update so that hapends after the record has been enter to the table. how can i do this?
just by changing the insert to UPDATE?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes:


create trigger trg_update_comm_and_gross_credit
ON bkpg
FOR UPDATE
AS
  IF @@NESTLEVEL = 1
  UPDATE bkpg
     set comm = CASE WHEN i.CXL='1' and i.COMM<>0 then i.COMM*-1 else i.COMM end
     , gross_credit = CASE WHEN i.CXL ='1' and i.GROSS_CREDIT <>0 then i.GROSS_CREDIT*-1 else i.GROSS_CREDIT end
 FROM bkpg t
 JOIN inserted i
   ON i.recordid = t.recordid

0
 
jsctechyAuthor Commented:
i did create the trigger and i execute my procedure to bring data to the table BUT when i execute this:
SELECT COMM, GROSS_CREDIT FROM BKPG WHERE CXL='1' it supposed to show me some negative number either COMM or GROSS_CREDIT but unfortunately isnt doing it why i dont know but isn't doing it.... =/
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
there are number of possibilities:
* the values are 0 or NULL
* the values inserted/updated are negative already, and pass to positive
* the trigger does not run, actually...
0
 
jsctechyAuthor Commented:
to answer your questions:
if i do run SELECT * FROM BKPG where comm =0
it does return records.

values are not negative alreadybecause this is the whole purpose on the trigger

does not run! it can be how can i force to run?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, we have to get back to this question:
can you show a INSERT/UPDATAE statement with the data you insert/update the table, and show the corresponding data you get right now, and the data you expect to get back.
0
 
jsctechyAuthor Commented:
ok here is a small example them:
if i do the following:

INSERT INTO BKPG
           ([ACCT_P]
          ,[COMM]
          ,[CXL]
)
     VALUES
           ('js300000'
           ,'100'
           ,'1'
)

I'm supposed to get on the table one record with COMM as negative hence CXL =1 then comm should be negative.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.