September 9, 2010 at 3:07 pm
If you can't get to the original code, here are a few things to consider:
1. If this is a third-party app, you may be violating the licensing agreement.
2. Do these 'extra" records need to be created in real time? near real time?
If real time, then the code in your trigger would look like this:
INSERT INTO tablename(field, field, field...)
SELECT field, new value, field...
FROM INSERTED
WHERE INSERTED.primary key field1 = X
AND INSERTED.primary key field2 = X
AND INSERTED.primary key field3 = X
There is no need to know what the PK values are, as the virtual table inserted is specific to the connection running the original code. So, since this is a NEW insert, not an update of the existing row, you do not have to join back to the original row.
If near real time:
Run a job that queries the table for the existance of rows that fit the critera, and insert new rows into the table
INSERT INTO table(x, y, z...)
SELECT X, Y, New Z, ...
FROM table
WHERE x = condition
AND y = condition
AND z = condition
AND NOT EXISTS (SELECT * FROM table WHERE primary key fields <> new values to be inserted)
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/
September 9, 2010 at 3:34 pm
thanks for the input.
the software is made by the IT team of the company where i work.
i have a new problem with the following code.
create 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.idcontrato 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
This trigger works with every record thats inserted in the table.
I need it to work only when the inserted records have IdContrato = 2 and EiCanalId = 91.
thanks in advance.
September 9, 2010 at 4:19 pm
For an easy answer, wrap the entire item with:
IF EXISTS (SELECT 1 from inserted where idcontrato='2' and eicanalid = '91')
BEGIN
--The rest of your trigger code goes here
END
A few significant issues I see here:
- You select two different values into the same variable, @canal.
- You select 7 times without a where clause from inserted, which you could do once and save some overhead.
- You're deleting from your entire existing table anything that matches your values. An instead of trigger, instead of an after trigger, would avoid this.
If you're comfortable with the performance and results, however, the if statement above should get you past the immediate issue.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 9, 2010 at 4:38 pm
Craig Farrell (9/9/2010)
For an easy answer, wrap the entire item with:
IF EXISTS (SELECT 1 from inserted where idcontrato='2' and eicanalid = '91')
BEGIN
--The rest of your trigger code goes here
END
thanks! that solve my problem!
pd:1 stands for??
A few significant issues I see here:
- You select two different values into the same variable, @canal.
that was a typo.
- You select 7 times without a where clause from inserted, which you could do once and save some overhead.
i will adress that.
- You're deleting from your entire existing table anything that matches your values. An instead of trigger, instead of an after trigger, would avoid this.
i will explain that in
If you're comfortable with the performance and results, however, the if statement above should get you past the immediate issue.
ill test it.
September 9, 2010 at 5:07 pm
The 1 is a literal value of 1. It's an optimization technique to not actually pull anything from the recordset to anywhere, since all you're doing is checking for existance, not a real value.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply