February 10, 2008 at 2:07 am
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
February 10, 2008 at 2:44 am
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
Learn to play, play to learn !
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[/url]
- 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
February 10, 2008 at 6:29 am
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: 😎 😀
February 10, 2008 at 10:23 am
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...
     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
[/font]Of course, since no one bothered to post the table schema nor any sample data, I've not tested the code above... 😛
http://www.sqlservercentral.com/articles/Best+Practices/61537/
--Jeff Moden
Change is inevitable... Change for the better is not.
February 11, 2008 at 6:35 am
Thanks Jeff for adding this little - but oh so crucial remark :w00t:
Johan
Learn to play, play to learn !
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[/url]
- 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
February 11, 2008 at 6:42 am
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
Change is inevitable... Change for the better is not.
February 11, 2008 at 2:05 pm
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
February 11, 2008 at 3:54 pm
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
Change is inevitable... Change for the better is not.
February 11, 2008 at 4:09 pm
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
February 11, 2008 at 4:28 pm
Heh... I love instant confirmation! Thanks, Chris!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply