Simple trigger (REALLY EASY)

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

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?

>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 ?

actually the store process is done by a batch process it does insert data as a batch.
using ADODB

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)

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

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?

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

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.... =/

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...

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?

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.

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.