Can not create Trigger for insert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Can not create Trigger for insert

Author

Topic

lydia
Starting Member

34 Posts

Posted - 2012-06-07 : 06:04:17

Hi everyone, Hope you all doing good.
I have one problem need hlep for all of you.
I have two tables

TB: vas_information
id int(autonumber),
cp_short_name,
cp_full_name,
vat_no,
address

TB:vas_information_history
sup_id,
datenote,
record_status,
id,
cp_short_name,
cp_full_name,
vat_no,
address

This is the Trigger i was create for insert

CREATE TRIGGER insert_vas_information
ON dbo.vas_information
AFTER INSERT
AS
BEGIN
declare @id int,
@cp_short_name varchar(35),
@cp_full_name varchar(50),
@vat_no varchar(20),
@address varchar(200)

select @id=id, @cp_short_name=cp_short_name, @cp_full_name=cp_full_name, @vat_no=vat_no, @address=address from inserted;
insert into vas_information_history (datenote, record_status, id, cp_short_name, cp_full_name, vat_no, address)
values(getdate(),'inserted',id, cp_short_name, cp_full_name, vat_no, address);
END
GO

***** This is error i got

Msg 128, Level 15, State 1, Procedure insert_vas_information, Line 14
The name 'id' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-07 : 06:08:40

id should be @id in the insert
Also your trigger can only deal with a single row update which is dangerous.
Why are you placing values in variables - why not just inserrt straight into vas_information from inserted.

insert into vas_information_history (datenote, record_status, id, cp_short_name, cp_full_name, vat_no, address)
select getdate(),'inserted',id, cp_short_name, cp_full_name, vat_no, address
from inserted

This will inserrt all rows included in the firing statement.
Have a look at
http://www.nigelrivett.net/index.html#Triggers

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lydia
Starting Member

34 Posts

Posted - 2012-06-07 : 06:15:57

I forget to tell you field sup_id is autonumber too
TB:vas_information_history
sup_id (autonumber),
datenote,
record_status,
id,
cp_short_name,
cp_full_name,
vat_no,
address

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-07 : 06:17:39

Shouldn't matter - it will be allocated automatically. Just don't reference it in the insert.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lydia
Starting Member

34 Posts

Posted - 2012-06-07 : 06:24:17

CREATE TRIGGER insert_vas_information
ON dbo.vas_information
AFTER INSERT
AS
BEGIN
declare @id int,
@cp_short_name varchar(35),
@cp_full_name varchar(50),
@vat_no varchar(20),
@address varchar(200)

insert into vas_information_history (datenote,record_status,id,cp_short_name,cp_full_name,vat_no,address)
select getdate(),'inserted',@id,@cp_short_name,@cp_full_name,@vat_no,@address from inserted;
END
GO

****I try as you told after now Command(s) completed successfully.

Thanks you very mucy for your help :)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-07 : 06:36:23

That will inserrt nulls as the variables have not been set
Have a look at my first post

CREATE TRIGGER insert_vas_information
ON dbo.vas_information
AFTER INSERT
AS
insert into vas_information_history (datenote, record_status, id, cp_short_name, cp_full_name, vat_no, address)
select getdate(),'inserted',id, cp_short_name, cp_full_name, vat_no, address
from inserted
GO

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lydia
Starting Member

34 Posts

Posted - 2012-06-22 : 05:30:43

Hi! Thanks for help me with Trigger.Follow your advice , Finally i can complete it . You are the best :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources