|
|
Posted by Jchick on 10/02/63 11:49
Thanks Erland Summarskog for the sample and input. I think you are
right, I need to have some kind of master database created first with a
unique customer number applied for each customer. I was trying to avoid
having to do that (adds an extra step in the beginning) but it will
make things easier in the long run.
Incidentally, I tried the code below and cannot seem to make it work
for populating the "mastertable". The Syntax is ok but something is
wrong with as I am getting "Error 31037, the name specified in the
Text property's 'CREATE...etc"
CREATE TRIGGER [Populate mastertable] ON [dbo].[ClientFile]
AFTER INSERT AS
INSERT mastertable (ProspectName)
SELECT i.ProspectName
FROM inserted i
WHERE NOT EXISTS (SELECT * FROM mastertbl.m WHERE m.ProspectName =
i.ProspectName)
=================
Thanks again for your assistance!!!
J
Erland Sommarskog wrote:
> 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]
|