To Add if not exists

Dear Experts,

I want to have a trigger so that Lndetail will always have the 1st entry upon saving the master (Ln) as follows:

When I encode and save this:
Ln Table:       LnNo (pK)       LnAmt      LnDate                  
                              1009      5000      5/14/2015                  
                                    
This will populate upon Ln Insert , if not yet exists.  The PayNum = Max number + 1
LnDetail:       LnNo (fK)       Debit      Credit      PayNum       Balance        Remark
                        1009              5000               0      1001       5000      1st Entry


Thanks.
JimiJ13I T ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

Can you please give an example that what exactly you want to do ?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Tell us what the logic is for populating columns Balance and Remark
0
Scott PletcherSenior DBACommented:
CREATE TRIGGER Ln__TRG_INSERT
ON Ln
AFTER INSERT
AS
SET NOCOUNT ON;
INSERT INTO LnDetail ( LnNo, Debit, Credit, PayNum, Balance, Remark )
SELECT i.LnNo, i.LnAmt, 0 AS Credit, 1001 AS PayNum, i.LnAmt, '1st Entry' AS Remark
FROM inserted i;
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

JimiJ13I T ConsultantAuthor Commented:
ScottPletcher,

Great! Can you please provide also the function for PayNum (max num + 1). I'm not using Identity column for it to avoid skipped numbers due to various reason.


Thanks.
0
JimiJ13I T ConsultantAuthor Commented:
Jim Horn,

From the main from transaction page where all conditions and agreements (no need to specify all of them) were set, then it goes down the detail transaction page where debit and credit trails are required. The first detail entry is a debit and should happen as the main entry is saved, the rest of the transactions will be handled by the web-app.  

Hope that makes sense.
0
JimiJ13I T ConsultantAuthor Commented:
Vikas Garg,

I think my original post has already the detail with a sample.


Thanks.
0
Scott PletcherSenior DBACommented:
>> ScottPletcher,
 Great! Can you please provide also the function for PayNum (max num + 1).  <<

Sure, sorry, I initially misunderstood what you needed.


CREATE TRIGGER Ln__TRG_INSERT
ON Ln
AFTER INSERT
AS
SET NOCOUNT ON;
INSERT INTO LnDetail (
    LnNo, Debit, Credit,
    PayNum,
    Balance, Remark
)
SELECT
    i.LnNo, i.LnAmt, 0 AS Credit,
    Max_PayNum + ROW_NUMBER() OVER (ORDER BY i.LnNo) AS PayNum,
    i.LnAmt, '1st Entry' AS Remark
FROM inserted i
CROSS JOIN (
    SELECT MAX(PayNum) AS Max_PayNum
    FROM LnDetail WITH (ROWLOCK, UPDLOCK)
) AS get_max_paynum;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JimiJ13I T ConsultantAuthor Commented:
ScottPletcher,

I think that's the one I am looking for. I will check it out and give you feedback.


Many Thanks.
0
JimiJ13I T ConsultantAuthor Commented:
Works perfect! Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.