?
Solved

SQL Trigger (with PK)

Posted on 2009-05-20
4
Medium Priority
?
239 Views
Last Modified: 2012-05-07
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
Comment
Question by:Navicerts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24429546
>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
 
LVL 7

Author Comment

by:Navicerts
ID: 24429597
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24429623
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
 
LVL 7

Author Comment

by:Navicerts
ID: 24429636
Ahh ok, I didn't know about this "JOIN INSERTED".

Thank You!
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question