Can anyone fix this please

Question

Can anyone fix this please

rahulsony111

Hall of Fame

Points: 3865
More actions
October 11, 2009 at 9:09 am

#73903

I wrote a code for that
But i got many syntax errors and may be logic errors too
i am beginner in SQL
can you please check the code and fix my errors
This is the code
CREATE TRIGGER <Schema1>.DIMCNAM
FOR DELETE
AS
IF (@@ROWCOUNT > 0)
INSERT INTO <Schema2>.DIMCNAM
SELECT ((SELECT column1 FROM DELETED), (SELECT column2 FROM DELETED), ‘P’, (SELECT column4 FROM DELETED), (SELECT column5 FROM DELETED), (SELECT column6 FROM DELETED),
(select column7 = (case when column7 = 'A' or 'B' then NULL else FHAFEQ end) from DELETED),
(select column8 = (case when column7 = 'A' or 'B' then NULL else FHAERS end) from DELETED),
(select column9 = (case when column7 = 'A' or 'B' then NULL else FHAFES end) from DELETED),
(select column10 = (case when column7 = 'A' or 'B' then NULL else FHAFET end) from DELETED),
(select column11 = (case when column7 = 'A' or 'B' then NULL else FHAFEU end) from DELETED), (SELECT column12 FROM DELETED), (SELECT column13 FROM DELETED), (SELECT column14 FROM DELETED), (SELECT column15 FROM DELETED), (SELECT column16 FROM DELETED), (SELECT column17 FROM DELETED), (SELECT column18 FROM DELETED), (SELECT column19 FROM DELETED), (SELECT column20 FROM DELETED), (SELECT column21 FROM DELETED), (SELECT column22 FROM DELETED), (SELECT column23 FROM DELETED), (SELECT column24 FROM DELETED), (SELECT column25 FROM DELETED), (SELECT column26 FROM DELETED), (SELECT column27 FROM DELETED), (SELECT column28 FROM DELETED), (SELECT column29 FROM DELETED))
END;

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

wow;

your issue seems to be you haven't recognized you can get all the values in a single select statement; there was no reason(or logic) to try to get each value in a seperate select.

here's how i interpreted your trigger should be:

CREATE TRIGGER MyTrigger on [Schema1].DIMCNAM

FOR DELETE

AS

--IF (@@ROWCOUNT > 0) --if a delete occurred, this would never be zero...no need to test for ir.

INSERT INTO [Schema2].DIMCNAM

SELECT

ROW_ID_NO,

WEB_TRX_DT,

'P',

PROC_STAT,

CNTRY_CD,

FHAEPA,

case when FHAFEQ IN('A','B') then NULL else FHAFEQ end,

case when FHAFEQ IN('A','B') then NULL else FHAERS end,--should this be case when FHAERS?

case when FHAFEQ IN('A','B') then NULL else FHAFES end,--should this be case when FHAFES?

case when FHAFEQ IN('A','B') then NULL else FHAFET end,--should this be case when FHAFET?

case when FHAFEU IN('A','B') then NULL else FHAFEU end,

FHA9E9,

FHAFE1,

FHAG3T,

FHAG3U,

FHBPMW,

FHBPMX,

FHBPMV,

FHBPNS,

FHAWQW,

FHAXKH,

FHAXKI,

FHAXKJ ,

FHAEQ0,

FHADVS,

FHAACX,

FHAE8O,

FHAE8M,

FHAE8N

FROM DELETED

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rahulsony111 Hall of Fame Points: 3865 More actions · Answer 2

I really appreciate your help man

Thank you very much for this

And about the IF statement

I have like this in my Specification

Original Value --------> INserted Value

column1 --------> column1

column2------> column2

column3 -------> 'P'

column4 -------> colum

column5 -------> column5

column6 --------> if column6 = 'A' or 'B' then NULL ELSE FHAFEQ

column7 --------> if column6 = 'A' or 'B' then NULL ELSE FHAERS

column8 --------> if column6 = 'A' or 'B' then NULL ELSE FHAFES

column9 --------> if column6 = 'A' or 'B' then NULL ELSE FHAFET

column10 --------> if column6 = 'A' or 'B' then NULL ELSE FHAFES

column11 --------> if column6 = 'A' or "B' then NULL ELSE FHAFEU

column12 --------> FHA9E9

........................................

........................................and so on

So all these depend on whether column6 = 'A' or 'B' then NULL otherwise their original value

Lowell SSC Guru Points: 323518 More actions · Answer 3

glad i could help;

that one section of the case statements i just wasn't sure about...you obviously know your data better than anyone, it just seemed like it might be a copy/paste error, since this is what i'd expect:

FHAFEQ --------> if FHAFEQ = 'A' or 'B' then NULL ELSE FHAFEQ

FHAERS --------> if FHAERS= 'A' or 'B' then NULL ELSE FHAERS

FHAFES --------> if FHAFES= 'A' or 'B' then NULL ELSE FHAFES

FHAFET --------> if FHAFET= 'A' or 'B' then NULL ELSE FHAFET

FHAFES --------> if FHAFES= 'A' or 'B' then NULL ELSE FHAFES

FHAFEU --------> if FHAFEU= 'A' or "B' then NULL ELSE FHAFEU

hope that helps what i was trying to say. good luck with your project!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!