Writing the trigger smarter 1

[Zargo]

Hi all,

I have written a trigger but i think this isn't a smart one. Can some of you check this and write this smarter?

CREATE TRIGGER [transfer] ON dbo.accounting FOR INSERT
AS
update accounting
set t7 = 0
from accounting a
inner join inserted I on a.docid=I.docid where
i.docid in (select docid from docs where invoicetype in ( '5','3','6','7' ))
update accounting
set t7 =
case
when (t4 is null and t5 is null) then cast(0 as varchar)
when t4 is null then cast(t5 as varchar) else cast(t4 as varchar)
end
where docid in (select docid from inserted) and docid in (select docid from docs where invoicetype not in ('3','6','7'))
update accounting
set t7 = (select suppliername from docs where docid in (select docid from inserted)) where docid in (select docid
from inserted)

Thanks in advance!!!

A big star for the golden solution

 

[SQLDenis]

set t7 =
case
when (t4 is null and t5 is null) then cast(0 as varchar)
when t4 is null then cast(t5 as varchar) else cast(t4 as varchar)
end


can be repaced with

set t7 =coalesce(t4,t5,0) -- this wil display the first non null value (0 if both t4 and t5 are null)

you don't need to convert to varchar an implicit conversion will occur if t7 is a varchar column

example
declare @v varchar(50)
select @v = 0
select @v

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Zargo]

SQLDenis,

Thanks for your reply. Your suggestion is just a part of the whole trigger. Do you (or anybody else) know how to write this smarter? I'm using a lot of select docid from inserted...

 

[SQLSister]

why not join to inserted instead of using subqueries?

Questions about posting. See faq183-874

 

[Zargo]

Hi SQLsister,

Thanks a lot for your reply. How to do that?

 

[Zargo]

Still waiting for the golden solution...

 

[Catadmin]

As far as SQLSister's suggestion, you already have most of the join to Inserted done, you just need to translate it to your other two update statements. Just keep following up on what you did before.

CREATE TRIGGER [transfer] ON dbo.accounting FOR INSERT
AS
update a
set t7 = 0
from accounting a
inner join inserted I on a.docid=I.docid 
inner join docs D
on a.DocID = D.DocID
where D.invoicetype in ( '5','3','6','7' )

update a
set t7 = coalesce(t4,t5,0) 
from accounting a
inner join inserted I on a.DocID = I.DocID
inner join docs D
on a.DocID = D.DocID
where docid not in ('3','6','7')

update a
set t7 = D.suppliername 
from accounting a
inner join docs D on a.DocID = D.DocID
inner join inserted I on a.DocID = I.DocID

I used SQLDenis's colease code in the above statement and I'm making the assumption that you've got three different updates you want to happen during an Insert of a record. If that's not correct, don't use my code.




Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"