trigger problem

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » trigger problem

trigger problem

Rate Topic

Display Mode

Topic Options

Author

Message

ejoeyz_85

Posted Sunday, February 10, 2008 2:07 AM

Grasshopper

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'

Post #453629

ALZDBA

Posted Sunday, February 10, 2008 2:44 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 6,861, Visits: 8,048

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

     Jul 13

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

- 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" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #453632

Ian Yates

Posted Sunday, February 10, 2008 6:29 AM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, September 16, 2012 3:26 AM
Points: 1,038, Visits: 443

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 w00t

Post #453642

Jeff Moden

Posted Sunday, February 10, 2008 10:23 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906, Visits: 26,789

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #453675

ALZDBA

Posted Monday, February 11, 2008 6:34 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 6,861, Visits: 8,048

Thanks Jeff for adding this little - but oh so crucial remark w00t

Johan

     Jul 13

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

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #453816

Jeff Moden

Posted Monday, February 11, 2008 6:42 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906, Visits: 26,789

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

Post #453820

GSquared

Posted Monday, February 11, 2008 2:05 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 15,442, Visits: 9,571

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

Jeff Moden

Posted Monday, February 11, 2008 3:54 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906, Visits: 26,789

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

Post #454118

Christian Buettner-167247

Posted Monday, February 11, 2008 4:09 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 1:06 AM
Points: 2,525, Visits: 3,618

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

Jeff Moden

Posted Monday, February 11, 2008 4:28 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906, Visits: 26,789

Heh... I love instant confirmation! Thanks, Chris!

--Jeff Moden

Post #454136

« Prev Topic | Next Topic »

Permissions