• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

SQL Trigger (with PK)

Hello,

I have two tables, one tracks the location of animals and one tracks the the death of the animal.  The table that tracks the location is keyed on (Animal ID + Date Inserted) and the one that tracks death is of course primary keyed on Animal ID.

I would like to make a trigger on the "death table" that removed the animal from its current location (updated (Date Removed).

One way I know to do this is just left join death table to the location table and update all NULL (Date Removed) fields to the same date as the mortality.  However, it seems like I would get better performance if I could avoid the join and simply do an update where Animal ID = (Animal ID of death record that was just added) instead of the broad sweeping statements I know how to do.  Also, multiple death records may be added at once.

Thanks

-Navicerts
0
Navicerts
Asked:
Navicerts
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>table that tracks the location is keyed on (Animal ID + Date Inserted)
you should have a main table with AnimalID as primary key, and the animal's global properties.
the locations table should have the AnimalID as foreign key, animal ID + date inserted as unique key or primary key, and the location information.
the death table could actually be just a column in the animal table itself ...


CREATE TRIGGER trg_death_animal
  ON animal_death
 FOR INSERT
AS
  UPDATE al
    SET removed_date = i.mortuality_date
    FROM animal_locations al
    JOIN INSERTED i
      ON al.animal_id = i.animal_id
    WHERE al.removed_date IS NULL
 

Open in new window

0
 
NavicertsAuthor Commented:
That is pretty much correct on the DB design.  However the mortality is in a separate table instead of on the "main" table, it just has a 1 > 1 relationship with FK tables off of the mortality table as well.  In retrospect yea I guess I could have just put the mortality in the "main" table as it has a 1 > 1 relationship but it does offer me some performance boosts in certain situations (albeit a performance hit in others).

The trigger you posted is the same as the way I already knew how to do a "mass update" if I am not mistaken?  It looks at all potential records in the Mortality table where birds have not been removed but I was looking for something that would update based off the specific animal id that was just updated as it would give better performance than a join + search for null?

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the JOIN INSERTED is doing the limitation for ONLY those that have been "updated" aka inserted into the mortuality table, based on the animal_id key.
it will not update ALL the rows from the locations table.
0
 
NavicertsAuthor Commented:
Ahh ok, I didn't know about this "JOIN INSERTED".

Thank You!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now