|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-12-13 : 08:19:44
|
| tony writes "Ok...here i go.First, i don´t speak (write) english as good as i´d like, so escuse me.I´m having lots of troubles with triggers in my database that i didn´t have with the stored procedures. Here´s an example of i´m trying to do. The thing is to create a registre in the table "trabajos" when i insert a new registre on the table "contratos" Looks simple, but i can´t!!*Is posible to asign the value of inserted table to a insert sentence?insert into trabajos (ref_contr) values (inserted.ref_contr)i don´t think so...*In the table "trabajos" i have the foreign key of "contratos" named ref_contr and the primary key named ref_trab (autoincrementated)For insert a new registre with the trigger i´m trying to do like this:-------------------------------------------CREATE TRIGGER t_contratos ON dbo.contratos FOR INSERTASdeclare @ref_contr intset @ref_contr = (select ref_contr from inserted) insert into trabajos (ref_trab) values (@ref_contr)-------------------------------------------The check is successful, but....-------------------------------------------*/*Error Type:Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)[Microsoft][ODBC SQL Server Driver][SQL Server]Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.--------------------------------------------CREATE TRIGGER t_contratos ON dbo.contratos FOR INSERTASdeclare cuinserted2 cursor for select * from inserted i inner join contratos c on (i.ref_contr = c.ref_contr);declare @ref_contr int, @num_contr varchar (20), @fech_contr datetime, @ref_ab int, @ref_agen int,@ref_inst int, @ref_us int, @est_contr int, @tipo_contr varchar(20), @so_contr varchar(20), @idm_contr varchar(20),@com_contr varchar(500), @ins_pref varchar(15), @ins_tjt varchar (15), @pdf_contr varchar (200)beginopen cuinserted2fetch from cuinserted2 into @ref_contr , @num_contr, @fech_contr, @ref_ab , @ref_agen,@ref_inst , @ref_us , @est_contr , @tipo_contr , @so_contr, @idm_contr,@com_contr, @ins_pref, @ins_tjt , @pdf_contr while @@fetch_status = 0 begin insert into trabajos (ref_contr) values (@ref_contr) fetch from cuinserted2 into @ref_contr , @num_contr, @fech_contr, @ref_ab , @ref_agen, @ref_inst , @ref_us , @est_contr , @tipo_contr , @so_contr, @idm_contr, @com_contr, @ins_pref, @ins_tjt , @pdf_contr endclose cuinserted2deallocate cuinserted2end----------------------------------------Here i had an extrange problem cause i have a cursor and when i finish i deallocated it. But the error says it already exists...Please help me!" |
|
TRIGGERS!! 