need to create an insert trigger

Question

need to create an insert trigger

ghazall_Alii

SSC-Addicted

Points: 454
More actions
August 10, 2010 at 12:11 am

#146502

Hi all
I need to write an insert trigger in sql server 2005.
i have table 'a' and a table 'b', i want when ever some record is inserted/updated in table_a , a new record is inserted in table_b such that the values for first two columns are hard coded then other columns have values of selected columns of inserted record.
for example
table_a has columns
name
f/name
age
DOB
Contact number
table_b has columns
table_name
status
name
age
f/name
now the record added to table_b on table_a insert/update should be
'table_a' (first column i-e tablename.This value is hard coded)
'inserted' (second column i-e status .This value is also hard coded)
value inserted in table_a for name
value inserted in table_a for age
value inserted in table_a for f/name
Any help would be highly appreciated.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

noone answered your post for a long time because you didn't provide the esentials...the CREATE TABLE definitions of your tables.

here is an example, but it's based on wild guesses as to what your table definitions really looks like:

CREATE TABLE table_a (

name varchar(312),

f_name varchar(41),

age int,

DOB datetime,

Contact_number varchar(83) )

CREATE TABLE table_b (

table_name varchar(312),

status varchar(19),

name varchar(312),

age int,

f_name varchar(41) )

GO

CREATE TRIGGER TR_TABLE_A_INSERTED

ON table_a

FOR INSERT

AS

INSERT INTO table_b(table_name,status,name,age,f_name)

SELECT

'table_a',

'inserted',

name,

age,

f_name

FROM INSERTED

--INSERTED is the virtual table in the trigger or OUTPUT clause which contains the new values.

--similarly, the DELETED table is there for update/delete triggers and OUTPUT clauses so you can have the old values as well.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ghazall_Alii SSC-Addicted Points: 454 More actions · Answer 2

@ Lowel Thank u so much for your response,u got it right and the solution u provided works exactly in the same way as i needed. 🙂

noone answered your post for a long time because you didn't provide the esentials...the CREATE TABLE definitions of your tables.

Lowell I define my problem in a descriptive form n i thought it was enough coz i wrote very clearly each n every point which could help solution-provider in understanding the problem(at least i think so 😎 ), thats y i didnt write sql statements here.