SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


trigger problem


trigger problem

Author
Message
ejoeyz_85
ejoeyz_85
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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
ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29341 Visits: 8986
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


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont 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 :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Ian Yates
Ian Yates
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4546 Visits: 445
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! Smile 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 Cool BigGrin



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211897 Visits: 41977
I'm going to say this so that everyone gets the point... Wink

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... Tongue
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29341 Visits: 8986
Thanks Jeff for adding this little - but oh so crucial remark w00t

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont 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 :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211897 Visits: 41977
Heh... I guess I get carried away a bit on the "little" stuff, Johan. Wink 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GSquared
GSquared
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56833 Visits: 9730
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211897 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5233 Visits: 3889
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211897 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search