473,395 Members | 1,689 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

Trigger to populate table or database

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
6 3600
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: June Moore | last post by:
Hi all, I have two tables tbl_A and tbl_B. Table Definitions: tbl_A (field_1, field_2, field_3) tbl_B (field_1, field_2) I would like to populate tbl_B when a record is inserted into tbl_A....
3
by: Luklrc | last post by:
Hi. I have a problem I hope someone can help me with. I have a database of events. Each event has a date and a duration (days). What I need to do is to be able to display search for events...
9
by: Martin | last post by:
Hello, I'm new with triggers and I can not find any good example on how to do the following: I have two tables WO and PM with the following fields: WO.WONUM, VARCHAR(10) WO.PMNUM,...
5
by: William of Ockham | last post by:
Hi, I was asked to recreate a new clean database for our developers because the current one they use is not entirely up to date. So I created a new database and I run into the followin strange...
0
by: gustavo_randich | last post by:
Hello, I'm looking for a DB2 workaround on a topic already solved in Oracle: the problem of mutating tables (which states that a trigger action cannot read the triggering table's data). Yes, I...
2
by: gustavo_randich | last post by:
Hi :-) I'm porting a project from Oracle to DB2 and now I'm trying to avoid error SQL0746N in a trigger which reads the same table in which the trigger is defined. Below is Oracle's...
2
by: mike | last post by:
I have a SQL based ERP package. Now I am having an issue on a particular table of people deleting a record (in the ERP system) and no one having any idea of who did it or when. So I thought I...
9
by: Ots | last post by:
I'm using SQL 2000, which is integrated with a VB.NET 2003 app. I have an Audit trigger that logs changes to tables. I want to apply this trigger to many different tables. It's the same trigger,...
7
by: RogBaker | last post by:
I haven't gotten a response yet, so I moved this from another group. I have been working on this for 2 days so if anyone has any ideas, I would be grateful. I have a 3rd party program that...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.