What is the proper structure for an insert after SQL trigger?

rwheeler23
rwheeler23 used Ask the Experts™
on
This is my first attempt at an INSERT AFTER trigger. What I need to happen is if the transactions are coming from timesheets(SOURDOC='PATS') I need to replace the employee ID(ORMSTRID) with another value that is held in a user defined field on the employee card(UPR00100). In the GL20000 table the jrnentry field is the primary key. This trigger appears to work but do I need to add anything to it for data validation?

create trigger [dbo].[ORMSTRID] on [dbo].[GL20000] after insert
as
begin


UPDATE dbo.GL20000
      SET ORMSTRID=T2.USERDEF1
      FROM dbo.GL20000 T1
      INNER JOIN dbo.UPR00100 T2 ON T1.ORMSTRID=T2.EMPLOYID
      INNER JOIN inserted ins on T1.JRNENTRY=ins.JRNENTRY
      WHERE T1.SOURCDOC='PATS'

end
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You could validate how many records were inserted and inserted values. The first check could be omitted in your case due to inner join in your update statement. The second type of check also seems to be unnecessary in your case because you transparently do it in your update statement, for example:
T1.SOURCDOC='PATS'
rwheeler23President

Author

Commented:
Thanks for the tip. It made me remember that I needed to add the SEQNUMBR field to the JOIN. This way only one record will be inserted at a time.

create trigger [dbo].[ORMSTRID] on [dbo].[GL20000] after insert
as
begin


UPDATE dbo.GL20000
      SET ORMSTRID=T2.USERDEF1
      FROM dbo.GL20000 T1
      INNER JOIN dbo.UPR00100 T2 ON T1.ORMSTRID=T2.EMPLOYID
      INNER JOIN inserted ins on T1.JRNENTRY=ins.JRNENTRY and T1.SEQNUMBR=ins.SEQNUMBR
      WHERE T1.SOURCDOC='PATS'

end
If you execute a statement like: insert into GL20000 (select ....), where select returns multiple records, inserted holds all these records. And I don’t think added condition “and T1.SEQNUMBR=ins.SEQNUMBR” could change number of records processed by this update statement. Each record in inserted presents in GL20000 and will be affected by this update.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

rwheeler23President

Author

Commented:
OK, so let's say jrnentry = 4321 and there are 4 sequence lines. Each line gets inserted one by one.
Seq line 1 gets inserted and I only want ORMSTRID to be updated on this one line. Isn't this what my insert trigger will do? It will only be doing one line at a time?
If multiple inserts based on statements like
insert into GL20000 (select ....)
are not expected. This extra condition is necessary and correct. Otherwise it won't help.
rwheeler23President

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial