Creating trigger with update

Hello

I am new to sql and asp, I am using visual web developer and have table that when it gets change I would like to see another table populated with the information. This is the two tables I have

First one has the information in it that users will insert in it

asset_id int Unchecked
asset_desc varchar(50) Checked
serial_no varchar(50) Unchecked
model_no varchar(50) Checked
category bigint Unchecked
Manufacturer varchar(50) Checked
Mac_address varchar(50) Checked
service_pack varchar(50) Checked
owner bigint Unchecked
location bigint Unchecked
date_acquired datetime Checked
date_deactivated datetime Checked
system_asset_no varchar(50) Checked
cs_desc varchar(50) Checked
vendor varchar(50) Checked
modified_date datetime Checked
action varchar(50) Checked
Unchecked

Next table is the one I want the information to go in

history_asset_id int Unchecked
history_asset_desc varchar(50) Checked
history_serial_no varchar(50) Checked
history_model_no varchar(50) Checked
history_category bigint Checked
history_manufacturer varchar(50) Checked
history_mac_address varchar(50) Checked
history_service_pack varchar(50) Checked
history_owner bigint Checked
history_location bigint Checked
history_date_acquired datetime Checked
history_date_deactivated datetime Checked
history_system_asset_no varchar(50) Checked
history_cs_desc varchar(50) Checked
history_vendor varchar(50) Checked
[modified date] datetime Checked
action varchar(50) Checked
Unchecked

the column action is for the name of person updating and modified date is the system date. My trigger is this

Create TRIGGER Trigger4
ON dbo.t_asset
FOR INSERT /* INSERT, UPDATE, DELETE */
AS
INSERT INTO history_asset_id
(history_asset_id, history_asset_desc, history_asset_orderno, history_asset_invoiceno, history_asset_yellowno, history_asset_serial_number,history_asset_cost, history_asset_fedpart, history_date_acquired, history_asset_cond, history_cat_id, history_bld_id, history_loc_name,history_date_deactivated, history_asset_dispvalue, action, modified_date)
VALUES ('@.asset_id','@.asset_desc','@.asset_orderno','@.asset_invoiceno','@.asset_yellowno','@.asset_serial_number','@.asset_cost','@.asset_fedpart','@.date_acquired','@.asset_cond','@.cat_id','@.bld_id','@.loc_name','@.date_deactivated','@.asset_dispvalue','@.action','@.sysdate')

Can anyone please help me or point me in the right direction, rbynum@.kansascommerce.com

Hi,

perhaps you first shoud read some concepts about triggers before implementing them.

Don′t wanna be a whippersnapper :-), this is a serious suggestion, most people (or even many) implemented triggers without having a basic understanding of them, like this, that triggers are fired on statement level rather than on a row level. So this can turn out to a real problem as data can be left in an inconsistent state if you don't care about that issue.

YOu will need the information and the understanding that information about the deleted / updated / inserted data is stored in virtual tables which are accessible at the following stages:

INSERT operation: INSERTED
UPDATE operation: INSERTED, DELETED
DELETE operation: DELETED

So in your case that would be something like:

INSERT INTO history_asset_id
(history_asset_id, history_asset_desc, history_asset_orderno, history_asset_invoiceno, history_asset_yellowno, history_asset_serial_number,history_asset_cost, history_asset_fedpart, history_date_acquired, history_asset_cond, history_cat_id, history_bld_id, history_loc_name,history_date_deactivated, history_asset_dispvalue, action, modified_date)
Select <columnlisthere>
FOM INSERTED

If you have any further question, don't hesitate to come back.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

You need to reference the inserted/updated/deleted rows inside the trigger code using the inserted/deleted virtual tables. See the Books Online topic on CREATE TRIGGER for more details. For the insert trigger above, you need to reference the inserted table like:

Create TRIGGER Trigger4
ON dbo.t_asset
FOR INSERT /* INSERT, UPDATE, DELETE */
AS
INSERT INTO history_asset_id
(history_asset_id, history_asset_desc, history_asset_orderno, history_asset_invoiceno, history_asset_yellowno, history_asset_serial_number,history_asset_cost, history_asset_fedpart, history_date_acquired, history_asset_cond, history_cat_id, history_bld_id, history_loc_name,history_date_deactivated, history_asset_dispvalue, action, modified_date)
select asset_id, asset_desc....

from inserted

sql