Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

trigger problem Expand / Collapse
Author
Message
Posted Sunday, February 10, 2008 2:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 03, 2008 5:01 AM
Points: 12, Visits: 43
I've some problem wif my triger statement... after execute this trigger, i got this message "Msg 156, Level 15, State 1, Procedure trig_updateOrganization, Line 28
Incorrect syntax near the keyword 'ELSE'."



What shud i do? Below is my trigger. Somebody help me....

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [trig_updateOrganization]

ON [dbo].[OrganizationDetail]
FOR UPDATE
AS

DECLARE @oldName VARCHAR(100)
DECLARE @newName VARCHAR(100)
DECLARE @oldAdress VARCHAR(100)
DECLARE @newAdress VARCHAR(100)

IF NOT UPDATE(Org_name)

BEGIN
RETURN
END


SELECT @oldName = (SELECT Org_name + ' ' FROM Deleted)
SELECT @newName = (SELECT Org_name + ' ' FROM Inserted)


PRINT 'Organization name changed from "' + @oldName +'" to "' + @newName + '"'
PRINT 'Have a nice day'


ELSE IF NOT UPDATE(Org_address)

BEGIN
RETURN
END


SELECT @oldAdress = (SELECT Org_address + ' ' FROM Deleted)
SELECT @newAdress = (SELECT Org_address + ' ' FROM Inserted)


PRINT 'Organization adress changed from "' + @oldAdress +'" to "' + @newAdress + '"'
PRINT 'Have a nice day'
T
Post #453629
Posted Sunday, February 10, 2008 2:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:10 AM
Points: 6,997, Visits: 8,410
you have messed up your if - sequence.

if not update(...)
begin
...
end
-- no other statements can exist between the end and the else
else -- elseif does not exist in tsql
begin
if not ...
...
end


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #453632
Posted Sunday, February 10, 2008 6:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
If I'm interpreting your logic correctly you should just get rid of the ELSE and you'll be fine. Actually, try this

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [trig_updateOrganization]

ON [dbo].[OrganizationDetail]
FOR UPDATE
AS

DECLARE @oldName VARCHAR(100)
DECLARE @newName VARCHAR(100)
DECLARE @oldAddress VARCHAR(100)
DECLARE @newAddress VARCHAR(100)

declare @Changed bit
set @Changed = 0

IF UPDATE(Org_name)
BEGIN

SELECT @oldName = (SELECT Org_name + ' ' FROM Deleted)
SELECT @newName = (SELECT Org_name + ' ' FROM Inserted)


PRINT 'Organization name changed from "' + @oldName +'" to "' + @newName + '"'
set @Changed = 1
END

IF UPDATE(Org_address)
BEGIN

SELECT @oldAddress = (SELECT Org_address + ' ' FROM Deleted)
SELECT @newAddress = (SELECT Org_address + ' ' FROM Inserted)

PRINT 'Organization address changed from "' + @oldAddress +'" to "' + @newAddress + '"'
END

if @Changed = 1
PRINT 'Have a nice day'




Thanks very much for using the code formatting block too - it makes it easier to read! :) Finally, why the two spellings of address? There's no spelling of it without the double-d in any dictionary I've seen. I'm a bit of a pedant in that regard (saw a sign today at a clothes store saying "Priced too clear" - didn't buy a thing!) so I changed it :D



Post #453642
Posted Sunday, February 10, 2008 10:23 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:59 AM
Points: 35,951, Visits: 30,239
I'm going to say this so that everyone gets the point... ;)

No, No, No, No!!!!

Now that I have your attention, the reason why I'm being so adamant is because all of the code on this thread, so far, is designed to handle one and only one row! That's a form of "Death by SQL" especially where triggers are concerned. You MUST ALWAYS write triggers to handle more than 1 row! It's not always a RBAR GUI that's going to be adding or modifying a row in a table... it could be a batch job that updates thousands of rows in one shot! The way the trigger examples have been written so far, I could update a million Org_Name and Address changes and only the first of each would be handled by the trigger. In other words, the trigger wouldn't even see the other 999,999 rows...

... actually, it would... the following two lines would give you an error about a sub-query in the Select list returning more than 1 value if you tried to update more than 1 row in the table...

SELECT @oldName = (SELECT Org_name + ' ' FROM Deleted) 
SELECT @newName = (SELECT Org_name + ' ' FROM Inserted)

Like I said, you MUST ALWAYS write triggers to handle more than just simple RBAR. Something like this would do...


ALTER TRIGGER [trig_updateOrganization]
     ON [dbo].[OrganizationDetail]
    FOR UPDATE 
     AS

     IF UPDATE(Org_name) 
--===== Return a complete list of ALL rows that changed names
     -- Special handling IS required to detect the first change
     -- because it starts out as NULL which cannot be compared directly.
  BEGIN
          PRINT 'The following organization names have changed...'
         SELECT d.Org_Name AS OldOrgName,
                i.Org_Name AS NewOrgName
           FROM DELETED d
          INNER JOIN INSERTED i
             ON d.PKColName = i.PKColName --<<LOOK!!! Change to correct column name!!!
          WHERE ISNULL(d.Org_Name,'') <> ISNULL(i.Org_Name,'')
          PRINT 'Have a nice day'
    END

     IF UPDATE(Org_address) 
--===== Return a complete list of ALL rows that changed addresses
     -- Special handling IS required to detect the first change
     -- because it starts out as NULL which cannot be compared directly.
  BEGIN
          PRINT 'The following addresses have changed...'
         SELECT d.Org_address AS OldAddress,
                i.Org_address AS NewAddress
           FROM DELETED d
          INNER JOIN INSERTED i
             ON d.PKColName = i.PKColName --<<LOOK!!! Change to correct column name!!!
          WHERE ISNULL(d.Org_address,'') <> ISNULL(i.Org_address,'')
          PRINT 'Have a nice day'
    END



Of course, since no one bothered to post the table schema nor any sample data, I've not tested the code above... :P
http://www.sqlservercentral.com/articles/Best+Practices/61537/


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #453675
Posted Monday, February 11, 2008 6:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:10 AM
Points: 6,997, Visits: 8,410
Thanks Jeff for adding this little - but oh so crucial remark


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #453816
Posted Monday, February 11, 2008 6:42 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:59 AM
Points: 35,951, Visits: 30,239
Heh... I guess I get carried away a bit on the "little" stuff, Johan. ;) But, you're correct... it's a crucial error that both Newbies and those that write Oracle triggers make in SQL Server.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #453820
Posted Monday, February 11, 2008 2:05 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
Beyond Jeff's correction to the trigger (very, very necessary), I have to question using "Print" in a trigger?

Is this schoolwork of some sort? The only way a Print command will every come up from a trigger is if you are doing an update in Query Analyzer/Management Studio, so far as I know. (Am I mistaken about that?)

In which case, either this trigger is some sort of demo, or you're telling the server to waste time and effort on an output that nobody will ever see.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #454068
Posted Monday, February 11, 2008 3:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:59 AM
Points: 35,951, Visits: 30,239
Print commands, like rowcounts, can sometimes be made to bubble up through the app.

However, I agree... I prefer to use Raiserror properly rather than the likes of PRINT... nothing should be returned to the app by a trigger, in my book, unless an error occurred.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #454118
Posted Monday, February 11, 2008 4:09 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:19 AM
Points: 2,814, Visits: 3,851
Just as a note, PRINT statements sometimes apear in the errors collection.
PRINT statements in SQL Server can also populate the ADO errors collection. However, PRINT statements are severity level zero (0) so, at least one RAISERROR statement is required in the stored procedure to retrieve a PRINT statement with ADO through the Errors collection.

http://support.microsoft.com/kb/194792/en-us


Best Regards,
Chris Büttner
Post #454127
Posted Monday, February 11, 2008 4:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:59 AM
Points: 35,951, Visits: 30,239
Heh... I love instant confirmation! Thanks, Chris!

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #454136
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse