August 31, 2009 at 9:35 am
Hi guys and girls!
Im just entering the SQL world and i find it great.
I´ve been asked to make an after update trigger.
This is the scenario;
When two rows get updated with specific data de trigger is triggered.:-D
When de data is validated i have to get data from another table and insert that data in another table called history. The data that goes on history has to be matched with exact same data, if exists, and then i must calculate prom and stdeup that´s no problem so far.
Mi problems, by now, are kinda simple, like a noob i guess 😀
1.- how can i validate simultaniously two rows with an IF statement??.
2.- Can i insert, in one INSERT sentence, data to diferent tables??.
3.- How can i validate that a set of data is exactly the same than any other??(PK doesn´t count) is there any system function that allows that?
Here is my transact. Is not too much but hopefully you can help me.
create trigger updateingmuestra on dbo.ingmuestra
after update not for replication as
begin
update dbo.ingmuestra
set dbo.ingmuestra.ingestado = inserted.ingestado
from inserted
if dbo.ingmuestra.ingestado = 1,dbo.ingmuestra.ingestado = 1
begin
insert into dbo.historia(hisidcliente,hispunto,hislugar)
select client_sample_id,sample_point,samlugar
from dbo.samples
go
insert into dbo.historia(scodaux)
select scodaux
from dbo.sclientes
go
insert into dbo.historia(hisfecha)
values (getdate())
end
end
August 31, 2009 at 9:40 am
If I understand you correctly, you want to check if there are two rows in the inserted table that have a specific value in one column. Is that correct?
If so, it looks something like this:
if exists
(select Col1
from inserted
where Col1 = DesiredValue
group by Col1
having count(Col1) > 1);
You have to use your real column names and the actual desired value, but that'll tell you if there are at least two rows with that value.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 31, 2009 at 9:46 am
GSquared (8/31/2009)
If I understand you correctly, you want to check if there are two rows in the inserted table that have a specific value in one column. Is that correct?If so, it looks something like this:
if exists(select Col1
from inserted
where Col1 = DesiredValue
group by Col1
having count(Col1) > 1);
You have to use your real column names and the actual desired value, but that'll tell you if there are at least two rows with that value.
Does that help?
thanks!! that solves one of my questions!! but...can i do this??
if exists
(select Col1,col2
from inserted
where Col1 = DesiredValue, col2 = DesiredValue2
group by Col1
having count(Col1) > 1);
thanks for the fast reply!!
August 31, 2009 at 10:06 am
You would need to add Col2 to the Group By clause, for that to work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply