help me am getting error in trigger

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)

help me am getting error in trigger

Author

Topic

shm
Yak Posting Veteran

86 Posts

Posted - 2008-11-11 : 08:05:47

hi,

i tried to write a trigger,but it is giving error.
if one is inserted to a table then that row should be inserted into another table which is diff database.

CREATE TRIGGER [INSERT_SWIPECARD]
ON [dbo].[SWIPECARD_MASTER]
AFTER INSERT
AS

BEGIN

INSERT INTO SEPG_TEST.SWIPE_CARD(EMP_SAMS_NO,DATE_IN,TIME_IN)
(SELECT EMP_LOG_ID,SMT_CREATED_DATE,
CASE WHEN SMT_TOTAL_IN_TIME IS NULL THEN '+'+'0 '+ '00:00' ELSE
'+'+'0 ' + RIGHT('0'+ CONVERT(VARCHAR(10),SMT_TOTAL_IN_TIME/60),2)+':'+RIGHT('0'+CONVERT(VARCHAR(10),(SMT_TOTAL_IN_TIME%60)),2) END TIME_IN
FROM EMPLOYEE E
INNER JOIN SWIPECARD_MASTER SM ON SM.SMT_EMP_SEQ_NO = E.EMP_SEQ_NO)

END

i tried to insert a one row into a swipecard_master but is giving error.

Msg 208, Level 16, State 1, Procedure INSERT_SWIPECARD, Line 14
Invalid object name 'SEPG_TEST.SWIPE_CARD'.

am not getting wt is the error?

readysetstop
Posting Yak Master

123 Posts

Posted - 2008-11-11 : 08:18:40

I am assuming that SEPG_TEST is the name of the other database AND dbo is the owner of that table in that database. If so, then it should be SEPG_TEST.dbo.SWIPE_CARD, rather than SEPG_TEST.SWIPE_CARD. See the section on Multipart names at http://msdn.microsoft.com/en-us/library/ms177563(SQL.90).aspx.

-D.

____________________________________________________________________________________
"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide

shm
Yak Posting Veteran

86 Posts

Posted - 2008-11-11 : 08:31:26

thank u it is not giving any error now
but wt it inserted is all the rows is inserted into another databse table ..am trying to write a trigger when 1 row is inserted into the table the same row should be isnerted into another database table

readysetstop
Posting Yak Master

123 Posts

Posted - 2008-11-11 : 11:44:15

Start here: http://msdn.microsoft.com/en-us/library/ms189799(SQL.90).aspx.

You're probably looking for something like this:


create trigger dbo.mytrigger
on mytable
INSTEAD OF insert
as

insert into mytable
select * from INSERTED

insert into myothertable
select * from INSERTED

go

HTH
-D.

____________________________________________________________________________________
"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide

shm
Yak Posting Veteran

86 Posts

Posted - 2008-11-12 : 01:06:18

thank u for ur help..

i made some change in the query now the trigger is working fine...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources