By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,412 Members | 1,630 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,412 IT Pros & Developers. It's quick & easy.

Trigger to populate table or database

P: n/a
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.

Any hints on how to get started on this?

Jun 2 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Am 2 Jun 2006 16:08:28 -0700 schrieb Jchick:
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.

Any hints on how to get started on this?


It is very easy to do this with a trigger. Read the OnlineManual (see
CRAETE TRIGGER), there are a lot of examples too.
But i don't see the sense of something. Because if i need a unique list of
customers from first table i do a "select distinct custName from table" and
have my result - no need for a master table. But it's your application :-)

bye, Helmut
Jun 3 '06 #2

P: n/a
Jchick (jc*********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 3 '06 #3

P: n/a
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 (jc*********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx


Jun 4 '06 #4

P: n/a
Jchick (jc*********@gmail.com) writes:
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"


Where do you get this error? What is the complete error message?
There is no error number 31037 in SQL Server, so I suspect that this
is an message your environment.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 4 '06 #5

P: n/a
I figured it out, I got the error trying to make a new trigger, but I
already had an existing trigger name in the selection box. It was a
bonehead mistake.

I'm on to new problems. Trying to learn how to set up the trigger to
send to an Access database's table. I was playing with this idea some
time ago and I believe you got me on the path to Linked Servers. So,
I've got a linked server to an Access database, one table with the
exact same 'fields'. I still want it to write only if the record is
unique. I'm trying to use the suggestions you gave me earlier:

CREATE TRIGGER [Populate AccessTable] 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!

Jchick


there but cannot remember the syntax to put in the trigger to go to a
linked
Erland Sommarskog wrote:
Jchick (jc*********@gmail.com) writes:
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"


Where do you get this error? What is the complete error message?
There is no error number 31037 in SQL Server, so I suspect that this
is an message your environment.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx


Jun 4 '06 #6

P: n/a
Jchick (jc*********@gmail.com) writes:
I'm on to new problems. Trying to learn how to set up the trigger to
send to an Access database's table. I was playing with this idea some
time ago and I believe you got me on the path to Linked Servers. So,
I've got a linked server to an Access database, one table with the
exact same 'fields'. I still want it to write only if the record is
unique. I'm trying to use the suggestions you gave me earlier:

CREATE TRIGGER [Populate AccessTable] 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)


In theory, just replace "mastertbl" with LINKEDSERVER...mastertbl. Maybe
you will have to put something between the dots, Access is not my realm.

But I would not be surprised if it fails, there is always a lots and
ifs and buts with linked server.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 4 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.