none
Insert Into Trigger "Multiple Tables" RRS feed

  • Question

  •  

    I have a trigger I am trying to build! I have 4 tables listed below. I basically add a program > Add attendees to the program > and then keep track of attendance!

     

    I need a trigger that when a Class is Cancelled from ProgramsCancelled it should insert the information into ProgramAttendance. My problem is getting the Id out of the ProgramAttendees Table where = ProgramCancelled.ClassId = ProgramAttendees.ClassId

     

    Declare @ClassId Varchar (4)

    Select @ClassId = ClassId from inserted

     

    Insert Into ProgramAttendance

     SELECT

              ID"< Problem", Date, 'Class Cancelled', Reason, @ClassId

     FROM inserted

     Where ?

     

    Programs "A table that stores a program and the schedule it runs"

    ClassId = PK

     

    ProgramsCancelled "A table that stores class cancellations"

    Rec = PK "Auto-Generating"

    ClassId = FK

     

    ProgramAttendees "A table that stores attendees in a class"

    Rec = PK "Auto-Generating"

    ClassId = FK

    Id = FK "Attendee Table"

     

    ProgramAttendance "A table that stores days the attendees are not in class"

    Rec = PK "Auto-Generating"

    ClassId = FK

    Id = FK "Attendee Table"

    Thursday, September 11, 2008 8:16 PM

Answers

  • Hi,

     

    Try something like this:

     

    Code Snippet

    insert into

    ProgramAttendance

    select

    pa.Id,

    Date,

    'Class Cancelled',

    Reason,

    @ClassId

    from

    inserted i

    inner join ProgramAttendees pa on i.ClassId = pa.ClassId

    where

    pa.ClassId = @ClassId

     

     

    Let us know if it works for you.

    Thursday, September 11, 2008 8:33 PM

All replies

  • Hi,

     

    Try something like this:

     

    Code Snippet

    insert into

    ProgramAttendance

    select

    pa.Id,

    Date,

    'Class Cancelled',

    Reason,

    @ClassId

    from

    inserted i

    inner join ProgramAttendees pa on i.ClassId = pa.ClassId

    where

    pa.ClassId = @ClassId

     

     

    Let us know if it works for you.

    Thursday, September 11, 2008 8:33 PM
  • Works great I love it!!!

     

    Friday, September 12, 2008 1:04 PM
  •  

    How do I do this same stuff as an update trigger?

     

    Declare @ClassId As Varchar(4)

    Declare @Id As Varchar(4)

    Declare @Date As DateTime

    Declare @Reason As Varchar(50)

     

    Select @ClassId = ClassId From Inserted

    Select @Id = Id From Inserted Where ClassId = @Id

    Select @Date = Date From Inserted Where ClassId = @Id

    Select @Reason = Reason From Inserted Where ClassId = @Id 

     

    Update ProgramAttendance

        set Id = @Id  ,

           Date = @Date,

           Reason = @Reason,   

           ClassId = @ClassId,

    Where (ProgramAttendance.Date = ProgramSchedule.Date) and (ProgramAttendance.ClassId = ProgramSchedule.ClassId)

    Friday, September 12, 2008 3:22 PM
  •  

    Got it Thanx!!!
    Friday, September 12, 2008 5:34 PM