reeeeeally easy for you guys!

can someone post me an example trigger where.. inserting a new record in one table, creates a new record in another table with a new unique identifier incrementing by 1 from the last record.

so that I can edit it to suit my own work like:

eg. when I insert new person into people table, using a person_no as key.
= creates new pay record in pay table ith a pay_id as key like '0004' 1 more than the last record if it was '0003', and then adds that new person_no to the record for reference.

I know this is a really easy trigger, but it would be my first! Any example to edit would be great! thanks in advancecreate table tablename2(
id int IDENTITY (1, 1),
value varchar(100)
)

CREATE TRIGGER Trigger ON TableName
FOR INSERT
AS
declare @.value1 varchar(100)
select @.value1=columnName from inserted

insert into tablename2 values (@.value1)

Originally posted by simple_simon
can someone post me an example trigger where.. inserting a new record in one table, creates a new record in another table with a new unique identifier incrementing by 1 from the last record.

so that I can edit it to suit my own work like:

eg. when I insert new person into people table, using a person_no as key.
= creates new pay record in pay table ith a pay_id as key like '0004' 1 more than the last record if it was '0003', and then adds that new person_no to the record for reference.

I know this is a really easy trigger, but it would be my first! Any example to edit would be great! thanks in advance|||I think I follow this, but my equivalent of identity and value are in different tables. I don't think I can apply this,

Which is the part of the code to increment new value by +1 of the last?|||Which is the part of the code to increment new value by +1 of the last?

The column 'id' is created as an IDENTITY field, which means the value in this column is automatically entered. It is set to start with 1 and increment by 1 each time a new record is inserted. Notice that the INSERT statement inserts data into the 'value' column only, the correct incremented integer for the 'id' column is calculated and inserted automaticlly.

This will work if you are creating new tables, if you are working with existing tables and data you would either have to ALTER the table or calculate the new value for 'id' yourself. Take a look at the following code. (I changed the name of your person_no column to people_id, standard naming conventions are a good habit to get into) The trigger will find the maximum current pay_id, increment it by 1, then insert it and the new people_id into the pay table

use northwind
go

CREATE TABLE people
(people_id int)
go

CREATE TABLE Pay
(pay_id int,
people_id int)
go

CREATE TRIGGER tr_insert_people ON people
FOR INSERT
AS
declare @.new_pay_id int
declare @.people_id int

select @.new_pay_id = ISNULL(max(pay_id),0) + 1 from pay
select @.people_id = people_id from inserted

insert into pay
(pay_id,people_id)
values
(@.new_pay_id,@.people_id)
go

INSERT INTO people
VALUES (1)

INSERT INTO people
VALUES (2)

INSER INTO people
VALUES (5)

SELECT * from people
SELECT * from pay

DROP TABLE people
DROP TABLE pay|||and what do you suppose will happen when you have this:

INSERT INTO people
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3

...or any other set based operation?