update when insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

update when insert

Author

Topic

programer
Posting Yak Master

221 Posts

Posted - 2014-10-05 : 11:46:05

hello

my code:
ALTER TRIGGER [dbo].[trg_InsertTransactionsUsers] ON [dbo].[tbl_TransactionsUsers]
FOR INSERT,UPDATE
AS
BEGIN

Declare @UserId UniqueIdentifier
Declare @Amount DECIMAL(18,2)
Declare @Fee DECIMAL(18,2)
Declare @Id int

IF EXISTS (SELECT UserId from tbl_UsersProfiles WHERE UserId=(Select UserId from deleted))
BEGIN

update tbl_TransactionsUsers
set AmountConverter =Amount/(SELECT Rate from tbl_currency_rate where Currency=(SELECT Currency from tbl_UsersProfiles where UserId= (SELECT Userid from inserted))),
FeeConverter = Fee/(SELECT Rate from tbl_currency_rate where Currency=(SELECT Currency from tbl_UsersProfiles where UserId= (SELECT Userid from inserted)))
where ID = (SELECT @@IDENTITY Inserted)

how can i used update when record is inserted?
Immedialtey when record is inserted i need to update AmountConverter...

My code not working because in my case is now ConverterAmount is NULL
And then if again update Amount column, then update in ConverterAmount...

Please help
Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-10-05 : 14:40:59

[code]ALTER TRIGGER dbo.trg_InsertTransactionsUsers
ON dbo.tbl_TransactionsUsers
AFTER INSERT,
UPDATE
AS

SET NOCOUNT ON;

IF UPDATE(Amount) OR UPDATE(Fee)
UPDATE tu
SET tu.AmountConverter = tu.Amount / cr.Rate,
tu.FeeConverter = tu.Fee / cr.Rate
FROM dbo.tbl_TransactionsUsers AS tu
INNER JOIN inserted AS i ON i.ID = tu.ID
INNER JOIN dbo.tbl_UsersProfiles AS up ON up.UserID = i.ID
INNER JOIN dbo.dbo.tbl_Currency_Rate AS cr ON cr.Currency = up.Currency;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-05 : 14:45:55

First of all, why the double-lookups?

(SELECT Rate from tbl_currency_rate where Currency=(SELECT Currency from tbl_UsersProfiles where UserId= (SELECT Userid from inserted)))

is in two lines. Yous should factor this out to an @Rate variable and use that in the update. Worse,those subqueries may return more than one row, depending on the UsersProfiles table and what gets inserted or updated. Remember that you can get a multiple rows in INSERTED and DELETED, and you need to program for that.

third, the update in the trigger couls fire the trigger recursively depending on the server trigger recursion setting

to tell if a row is inserted test for

if exists (select * from inserted) ...

Testing for UPDATE or INSERT Actions to Specific Columns
You can design a Transact-SQL trigger to perform certain actions based on UPDATE or INSERT modifications to specific columns. Use UPDATE() or COLUMNS_UPDATED in the body of the trigger for this purpose. UPDATE() tests for UPDATE or INSERT tries on one column. COLUMNS_UPDATED tests for UPDATE or INSERT actions that are performed on multiple columns and returns a bit pattern that indicates which columns were inserted or updated.

http://msdn.microsoft.com/en-CA/library/ms189799.aspx

also, you can use something like IF EXISTS (SELECT * FROM DELETED) to detect an update. You only have rows in DELETED on update (or delete), but there are always rows in INSERTED for insert and update triggers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources