Solved

SQL Trigger (with PK)

Posted on 2009-05-20
4
230 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
Comment Utility
>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
Comment Utility
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]
Comment Utility
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
Comment Utility
Ahh ok, I didn't know about this "JOIN INSERTED".

Thank You!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now