I need to update table1.field1 From table2.field2 everytime there is a new entry on table2. I created a trigger that does exactly that :
CREATE [dbo].[insert_trg]
on [dbo].[TheCat99]
AFTER INSERT AS
BEGIN
Update therefore.dbo.thecat99
SET factura = (SELECT dbo.pedido.num_factura
From dbo.Pedido
Where dbo.thecat99.pedido=dbo.pedido.num_pedido)
WHERE dbo.thecat99.pedido = ( SELECT dbo.pedido.num_pedido
FROM dbo.pedido
Where dbo.thecat99.pedido = dbo.pedido.Num_Pedido)
AND dbo.thecat99.factura is NULL
END
The trigger Works but it's not in production yet.I've been reading about how triggers are bad and evil, but i cant see how can i do this using stored procedures. Is the trigger a good idea? If not, how can i do the same with a stored procedure?
Triggers are not bad nor are they evil. They are a great tool (and for some tasks, the only tool) but they do require knowledge and careful use, since they are not as simple to write as they seem at first glance.
As a general rule, Triggers should be as light-weight and effective as possible. This is because SQL Server will not return control to whoever executed the statement that fired the trigger until the trigger is also completed.
Having said that, your update code could be written like this (that will be more efficient and more readable):
UPDATE t
SET factura = p.num_factura
FROM therefore.dbo.thecat99 t
INNER JOIN dbo.Pedido p ON t.pedido= p.num_pedido
WHERE t.factura IS NULL
However Since you are not using the inserted table, It means that every time any row gets inserted into TheCat99 all the rows where the pedido value matches the num_pedido in Pedido will be used for the update. There is still room for improvement - using the inserted table instead of thecat99 means you will only be working with the records that was just inserted to the thecat99 table, so your code will be far more efficient:
UPDATE t
SET factura = p.num_factura
FROM inserted t
INNER JOIN dbo.Pedido p ON t.pedido= p.num_pedido
WHERE t.factura IS NULL