|
|
Posted by Erland Sommarskog on 06/03/06 10:53
Jchick (jchickering@gmail.com) writes:
> Im a newbie with a sql table in a MSDE database that contains fields of
> CustName, BrokerName, Type, Status.
>
> I am trying to write a trigger that does this:
>
> 1. When a new record in a table is added, I would like the selected
> fields to be copied to another table (call it Master).
> 2. If that Master table already contains a record where the CustName =
> CustName, then simply ignore. If CustName does not match anything, then
> add the record to the Master Table
>
> In other words, I am trying to create a table (or even a new database)
> that has unique records in it - making a master customer list.
The trigger could read:
CREATE TRIGGER Jchkicks_trigger ON tbl AFTER INSERT AS
INSERT mastertbl (CustName)
SELECT i.CustName
FROM inserted i
WHERE NOT EXISTS (SELECT *
FROM mastertbl m
WHERE m.CustName = i.CustName)
The table "inserted" that appears here is a virtual table that holds
the inserted rows. For DELETE triggers there is a "deleted" table which
holds the deleted rows, and in an UPDATE triggers both tables are
populated: "inserted" with the rows after the update, and "deleted"
with the rows before the update.
It's also important to understand that a trigger fires once per statement,
so the trigger must be able to handle multi-insert operations.
As for the actual purpose of the trigger, it does not really sounds like
a good design to me. A more normal design would be that the customers
were added first into a Customers table. That table would have various
customer information like name, address, shoe size or whatever you need
to store. It would also define a key for the customer, one way or another.
Then when you need to add a customer into a table like this one, you would
use the key value from the Customers table, and the referencing table
would have a foreign key reference to the Customers table to ensure
that you don't insert non-existing keys, or delete customers that are
referenced.
With this design you could end up with "Erland Sommarskog" and
"Sommarskog, Erland" in the master table which would not tell you
very much.
And you could just as well populate the master table with
SELECT DISTINCT CustName FROM tbl
when you needed it.
Then again, I don't know your business requirements, so maybe it makes
sense after all.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|