Trigger Question - 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 2005 Forums

Transact-SQL (2005)

Trigger Question

Author

Topic

jtrumbul
Starting Member

11 Posts

Posted - 2008-12-09 : 11:07:25

Hello All i have a question about creating a trigger. I need to have a trigger on a timesheet table so when a record is inserted the trigger will search for that job in another database on the same server, if a record is found with that job id i need to duplicate the timesheet entry in another timesheet table on the second database.. I need to know how i can switch the database im updating in the middle of a trigger. So for example:

db1.Timesheet table
jobid
mondaytime
tuesdaytime
wedtime
thurstime
fridaytime
sattime
suntime

when a record is entered in the above table i need to goto

db2.Timesheet
check for a jobid match if one is found i need to take the same time entry and put it in db2.Timesheet table

If there is a better way then a trigger to do so please let me know.

any help is appreciated,
John

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 11:09:43

are dbs in same server? if yes, you can simply use db2.dbo.table to refer to other db table. but keep in mind that cross database triggers will have an impact on performance.
b/w,DO you want this to happen everytime a record is inserted?

jtrumbul
Starting Member

11 Posts

Posted - 2008-12-09 : 11:24:55

they are on the same server. I do want this to happen everytime a record is inserted. essentially the issue is i have 2 seperate time management applications yet they handle most of the same projects, so everytime i record is inserted in db1 i need to check db2 based on the jobid and see if that job exists. If it does i then need to update db2's timesheet table with the same information that was inputted in db1 timesheet table to keep an accurate time log on both databased for the project. Im not even sure how to begin implementing this solution, or if its even much of a solution at all.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 11:31:49

then i think you can go with trigger itself

jtrumbul
Starting Member

11 Posts

Posted - 2008-12-09 : 11:36:48

ok so my thinking is i will need to write it in the following manner please let me know if im way off:

create a variable for jobid - populate it with a select statement from the inserted table to get the value of the new record
then i need to do something like @recctr = (select count(*) from db2.dbo.timesheet where jobid = @jobid) then if @recctr > 0 insert into db2.dbo.timesheet(inserted record values) ?

also what happens if a batch update is run on the timesheet table in db1 will the trigger handle all new records entered on a 1 to 1 basis without screwing up?

sorry for the questions if there silly SQL is new to me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 11:46:25

Nope. no need of variablke just something like


CREATE TRIGGER ON timesheet
AFTER INSERT
AS
INSERT INTO db2.dbo.Timesheet
SELECT t.jobid,
t.mondaytime,
t.tuesdaytime,
t.wedtime,
t.thurstime,
t.fridaytime,
t.sattime,
t.suntime
FROM INSERTED i
JOIN db2.dbo.Timesheet t
ON t.jobid=i.jobid

jtrumbul
Starting Member

11 Posts

Posted - 2008-12-09 : 11:48:34

wow i guess i didnt think it could be that easy! thanks for the help i will try it out if i have any issues i will post them here.

thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 11:52:31

no worries...let me know how you got on

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources