Solved

SQL Trigger (with PK)

Posted on 2009-05-20
4
232 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
  • 2
  • 2
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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 142

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

776 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