[dba-SQLServer] Triggers To Update A Table When Another Table Has A Record Inserted

This trigger inserts to an audit trail table from my prospect table.  If it
is a new or different record then over it goes.

insert prospect_archive (prospect_no, change_user, change_date, mktsrc_id,
prospstat_id,
	lead_emp_no, sales_emp_no, assign_emp_no, promo_id)
select p.prospect_no, p.change_user, @change_date, p.mktsrc_id,
p.prospstat_id,
	p.lead_emp_no, p.sales_emp_no, p.assign_emp_no, p.promo_id
from inserted, prospect p
where p.prospect_no = inserted.prospect_no
and not exists (select prospect_no from prospect_archive
	where prospect_no = inserted.prospect_no
	and isnull(mktsrc_id, '') = isnull(p.mktsrc_id, '')
	and isnull(prospstat_id, '') = isnull(p.prospstat_id, '')
	and isnull(lead_emp_no, 0) = isnull(p.lead_emp_no, 0)
	and isnull(sales_emp_no, 0) = isnull(p.sales_emp_no, 0)
	and isnull(assign_emp_no, 0) = isnull(p.assign_emp_no, 0)
	and isnull(promo_id, '') = isnull(p.promo_id, '') )

-----Original Message-----
From: paul.hartland at fsmail.net [mailto:paul.hartland at fsmail.net]
Sent: Friday, April 02, 2004 9:17 AM
To: dba-sqlserver
Subject: [dba-SQLServer] Triggers To Update A Table When Another Table
Has A Record Inserted


To all,
 
Just starting to play about with Triggers, what I want to do is create a
Trigger for a table called tblPersonnel so that when someone adds a new
record to the table, the Trigger Executes a stored procedure that updates a
table called tblDataChanges with the PayrollNo, DateAdded, Firstname &
Surname.
 
Has anyone got any sample code to create this Trigger, I have the Stored
Procedure to update the tblDataChanges with the Parameters @Payroll,
@DateAdded, @Firstname & @Surname.
 
Thanks in advance for any help on this.
 
Paul Hartland
Freeserve AnyTime - HALF PRICE for the first 3 months - Save £7.50 a month 
www.freeserve.com/anytime
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com