[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Simple trigger (REALLY EASY)

Posted on 2007-10-10
14
Medium Priority
?
242 Views
Last Modified: 2010-03-19
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
0
Comment
Question by:jsctechy
  • 7
  • 7
14 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20051508
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
 
LVL 1

Author Comment

by:jsctechy
ID: 20051616
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20051648
>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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 1

Author Comment

by:jsctechy
ID: 20051708
actually the store process is done by a batch process it does insert data as a batch.
using ADODB
0
 
LVL 1

Author Comment

by:jsctechy
ID: 20051714
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20051753
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
 
LVL 1

Author Comment

by:jsctechy
ID: 20051794
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20051868
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
 
LVL 1

Author Comment

by:jsctechy
ID: 20051970
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20052013
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
 
LVL 1

Author Comment

by:jsctechy
ID: 20052111
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20052136
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
 
LVL 1

Author Comment

by:jsctechy
ID: 20052302
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20054333
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Integration Management Part 2
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question