after insert trigger

Question

Post reply

after insert trigger

Viewing 15 posts - 16 through 30 (of 34 total)

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

igngua SSCrazy Points: 2510 More actions · Answer 1

Ok it seems i´ve not been the only with this problem.

I found on the web the following;

create table fred

(fredId int primary key,

value varchar(10))

go

create trigger fred$insertTrigger

on fred

after insert

as

update fred

set fredId = fredId * 10000

where fredId in (select fredId from inserted)

go

Ideas?

Lowell SSC Guru Points: 323518 More actions · Answer 2

if the three columns make the row unique, why are you changing one of the values? a trigger like that should change columns other than those comrising the primary key.

if you are going to do that, this should be an INSTEADOF INSERT trigger, not an AFTER INSERT

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!

igngua SSCrazy Points: 2510 More actions · Answer 3

Lowell (9/9/2010)
if the three columns make the row unique, why are you changing one of the values? a trigger like that should change columns other than those comrising the primary key.
if you are going to do that, this should be an INSTEADOF INSERT trigger, not an AFTER INSERT

if i do an instead of trigger that would delete the original record. I dont want the original record to be deleted. I want a new record that takes all the data from from the recently inserted record where idcontrato =2 and eicanalid =91. And then give this new record a new idcontrato.

igngua SSCrazy Points: 2510 More actions · Answer 4

igngua

SSCrazy

Points: 2510

September 9, 2010 at 10:31 am

#1218775

any ideas?

Michael L John One Orange Chip Points: 27060 More actions · Answer 5

A trigger certainly does not seem to be the best method to use to accomplish what needs to be done.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Lowell SSC Guru Points: 323518 More actions · Answer 6

igngua (9/9/2010)
if i do an instead of trigger that would delete the original record. I dont want the original record to be deleted. I want a new record that takes all the data from from the recently inserted record where idcontrato =2 and eicanalid =91. And then give this new record a new idcontrato.

if you want a new record, you need to INSERT, not update the existing.

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!

igngua SSCrazy Points: 2510 More actions · Answer 7

Michael L John (9/9/2010)
A trigger certainly does not seem to be the best method to use to accomplish what needs to be done.

why?

igngua SSCrazy Points: 2510 More actions · Answer 8

Lowell (9/9/2010)
igngua (9/9/2010)
if i do an instead of trigger that would delete the original record. I dont want the original record to be deleted. I want a new record that takes all the data from from the recently inserted record where idcontrato =2 and eicanalid =91. And then give this new record a new idcontrato.
if you want a new record, you need to INSERT, not update the existing.

can you give me an example please.?

Lowell SSC Guru Points: 323518 More actions · Answer 9

lets try something different...show us what you think the INSERT INTO statement would look like;

your requirement changed form updating colA and colB to something different....all the clues for your trigger are there...i'd like to see some input from you this time.

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!

igngua SSCrazy Points: 2510 More actions · Answer 10

Lowell (9/9/2010)
lets try something different...show us what you think the INSERT INTO statement would look like;
your requirement changed form updating colA and colB to something different....all the clues for your trigger are there...i'd like to see some input from you this time.

ok.. here i go...

insert into EICANALESRES

select * from inserted

where idcontrato= 2

and eicanalid = 19

My question using the insert is; how do i change the value from idcontrato from '2' to '13'

thanks.

Michael L John One Orange Chip Points: 27060 More actions · Answer 11

You said:

I want a new record that takes all the data from from the recently inserted record where idcontrato =2 and eicanalid =91. And then give this new record a new idcontrato.

I understand this to mean that you ACTUALLY need to insert TWO records in the table, if the idcontrato and eicanalid values are 2 and 91 respectively.

All of the fields will contain identical data except for the idcontrato field.

So, would it not make sense to create a second insert statement if the proper conditions are met when the first insert statement occurs?

Plus, and I may get a few arguments here, if you trying to put business logic in a trigger, then you are asking for trouble. What is the primary purpose of a trigger? To enforce data integrity.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

igngua SSCrazy Points: 2510 More actions · Answer 12

Michael L John (9/9/2010)
You said:
I want a new record that takes all the data from from the recently inserted record where idcontrato =2 and eicanalid =91. And then give this new record a new idcontrato.

absolutely right.

I understand this to mean that you ACTUALLY need to insert TWO records in the table, if the idcontrato and eicanalid values are 2 and 91 respectively.
All of the fields will contain identical data except for the idcontrato field.
So, would it not make sense to create a second insert statement if the proper conditions are met when the first insert statement occurs?

i understand that but i dont know how to do it, i get lost in the syntax.

im kinda noob.:-)

Michael L John One Orange Chip Points: 27060 More actions · Answer 13

Can you post the original insert statement/procedure that performs the first insert?

It's probably will be something like:

INSERT INTO Table (field, field,...)

VALUES(x, y, ...)

If x = 91 and y = 3 Begin

INSERT INTO Table (field, field,...)

VALUES(New X value, y, ...)

End

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

igngua SSCrazy Points: 2510 More actions · Answer 14

Michael L John (9/9/2010)
Can you post the original insert statement/procedure that performs the first insert?
It's probably will be something like:
INSERT INTO Table (field, field,...)
VALUES(x, y, ...)
If x = 91 and y = 3 Begin
INSERT INTO Table (field, field,...)
VALUES(New X value, y, ...)
End

the first insert is made by a program. I dont know how it does it.

igngua SSCrazy Points: 2510 More actions · Answer 15

UFFF! ive made it...after a lot of effort.

thanks for your help.

now i share.

alter TRIGGER trgAfterInsert ON eicanalesres

FOR INSERT

AS

declare @contrato decimal (10,0);

declare @canal decimal (10,0);

declare @servidor datetime;

declare @maquina datetime;

declare @corregida datetime;

declare @fisico decimal (25,4);

declare @numerico decimal (25,4);

declare @calculado decimal (25,4);

declare @estado smallint

delete from eicanalesres

where idcontrato = '13' and

eicanalid = '91' and

eivalorfis = '0' and

eivalornum = '0'

select @canal = i.eicanalid from inserted i where idcontrato ='2';

select @canal = i.eicanalid from inserted i where eicanalid ='91';

select @servidor = i.eifechserv from inserted i;

select @maquina = i.eifechmaq from inserted i;

select @corregida = i.eifechcorr from inserted i;

select @fisico = i.eivalorfis from inserted i;

select @numerico = i.eivalornum from inserted i;

select @calculado = i.eivalorcalc from inserted i;

select @estado = i.eiest from inserted i;

insert into eicanalesres

(IdContrato,

EiCanalId,

eiFechServ,

EiFechMaq,

EiFechCorr,

EiValorFis,

EiValorNum,

EiValorCalc,

EiEst)

values('13',

@canal,

@servidor,

@maquina,

@corregida,

@fisico,

@numerico,

@calculado,

@estado);

GO