question

eaquiroz avatar image
eaquiroz asked

SQL Server Trigger Question

I have SQL Server trigger question that I was wondering if you can assist. I have two tables (TableA & TableB) that are updated together with a one to many relationship. When a record is inserted to TableA it may or may not have multiple entries to TableB. I need to write a trigger that once a new record is created in both tables that the lowest number entry from tableB.Idx has to be copied to TableA.MainHolder CREATE TRIGGER EddieTest ON TableB AFTER INSERT AS BEGIN SET NOCOUNT ON; declare @value int select @value=TableB.Idx from inserted if @value is not null begin update TableA set TableA.MainHolder=@value end END GO ![alt text][1] [1]: /storage/temp/2466-trigger.png
sql servertrigger
trigger.png (388.8 KiB)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Why you store same data into different tables? Is it necessary? MainHolder column in TableA store data that you already have in another table. You can SELECT MainHolder anytime you need, with one simple query. Everybody are trying to avoid triggers...
0 Likes 0 ·

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
The first thing to realise is that a trigger is fired once per transaction, not per row, so you need to handle the data as though there are multiple rows (even if there are not). So here I get the `AcctIdx` from `inserted`, join back to `TableB` to get the lowest `Idx` (`min(TableB.Idx)`), then join that result set onto `TableA` and update `MainHolder` create trigger EddieTest on dbo.TableB after insert as begin update TableA set MainHolder = LowestEntry.LowestEntryIdx from dbo.TableA TableA join ( select inserted.AcctIdx, min(TableB.idx) as LowestEntryIdx from inserted join dbo.TableB TableB on TableB.AcctIdx = Inserted.AcctIdx group by inserted.AcctIdx ) LowestEntry on LowestEntry.AcctIdx = TableA.idx end
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes it worked you are awesome!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.