SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update Trigger for SQL Server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Vinamr
Starting Member

3 Posts

Posted - 08/09/2010 :  16:44:53  Show Profile  Reply with Quote
Hi Experts,

I have a registration table where members and their childrens registration info is stored. I would like to assign a specific value to the child enrolled field when the parent Enrolled field is updated in the table. The below code is only executing the first update.

For Example: When the Adult Enrolled field is updated to 1000 I want the Child Enrolled field to be updated to 100 (same with 2000/200, 3000/300)


create trigger UPDATE_BV
on [dbo].[Registrations] 
for update as
if update(Child_Enrolled)
begin
     update [dbo].[Registrations] 
           set Child_Enrolled = '100'
           where Adult_Session_Enrolled = '1000'
     update [dbo].[Registrations] 
           set Child_Enrolled = '200'
           where Adult_Session_Enrolled = '2000'
     update [dbo].[Registrations] 
           set Child_Enrolled = '300'
           where Adult_Session_Enrolled = '3000'
end


Any pointers .. Thanks
Vinny

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 08/09/2010 :  16:54:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
You want: if update(Adult_Enrolled)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Vinamr
Starting Member

3 Posts

Posted - 08/09/2010 :  17:59:21  Show Profile  Reply with Quote
Thanks for the pointer. That worked.

Vinny
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 08/09/2010 :  18:07:56  Show Profile  Visit tkizer's Homepage  Reply with Quote
You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/10/2010 :  02:43:57  Show Profile  Reply with Quote
Note that "if update(Adult_Enrolled)" will tell you that [Adult_Enrolled] was part of the UPDATE statement, but not that the specific row actually changed value.

You also need to make use of the Deleted and Inserted pseudo tables which contain the Before/After copies of EACH/EVERY row in the update. You are updating the whole table (which may be what you want, but is not specific to the rows that were just updated)

update U
           set Child_Enrolled = CASE I.Adult_Session_Enrolled 
                                     WHEN '1000' THEN '100'
                                     WHEN '2000' THEN '200'
                                     WHEN '3000' THEN '300'
                                     ELSE Child_Enrolled -- If something goes wrong! leave the original value there
                                     END
FROM [dbo].[Registrations] AS U
    JOIN Inserted AS I
        ON I.MyID = U.MyID
    JOIN Deleted AS D
        ON D.MyID = U.MyID
WHERE COALESCE(D.Adult_Session_Enrolled, '') <> COALESCE(I.Adult_Session_Enrolled, '') -- Column has changed
      AND I.Adult_Session_Enrolled IN ('1000', '2000', '3000')
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000