trigger problem

Question

Post reply

trigger problem

ambrose_sql

Grasshopper

Points: 21
More actions
April 22, 2004 at 12:49 am

#108281

Here is my trigger code.
CREATE TRIGGER insert_into_emp ON emp
FOR INSERT,UPDATE,DELETE
AS
SET XACT_ABORT ON
if not exists(select emp_id,dept_id from deleted)
begin
INSERT INTO aaa.bbb.dbo.emp(emp_id,emp_name,emp_address,dept_id)
SELECT emp_number as emp_id,emp_full_name as emp_name,emp_address,dept_id
FROM inserted
return
end
if not exists(select emp_id,dept_id from inserted)
begin
delete FROM aaa.bbb.dbo.emp a INNER JOIN deleted d
ON a.emp_id=d.emp_number and a.dept_id=d.dept_id
return
end
UPDATE a SET a.emp_name=i.emp_full_name
a.emp_Address=i.emp_address
FROM aaa.bbb.dbo.emp a INNER JOIN inserted i
ON a.job_number=i.job_id AND a.event_id=i.event_id
In my table combination of emp_id and dept_id makes a row unique.Its a compsite key.
emp_id dept_id emp_name emp_Address
1 1 hh jj
1 2 uu ii
2 2 jj kk
3 1 ll ee
4 1 mm oo
3 2 ss tt
I wanted to update the record for emp_id ='3' and dept_id='1'.My trigger is not working for that and giving an error saying that' The database row you are modifying no longer exists in the database'
Can anyone correct my code.

Is't possible to update any of the rows in my table.siupoose i update the row
1 2 uu ii
How can i update the same row in my target table with the trigger,Is't possible to implement?
Can anyone help me in this issue.
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

kokyan SSCommitted Points: 1694 More actions · Answer 1

Hi ambrose_sql,

I'm not very understand what is the meaning of the part in green color:

CREATE TRIGGER insert_into_emp ON emp

FOR INSERT,UPDATE,DELETE

AS

SET XACT_ABORT ON

if not exists(select emp_id,dept_id from deleted)

begin

INSERT INTO aaa.bbb.dbo.emp(emp_id,emp_name,emp_address,dept_id)

SELECT emp_number as emp_id,emp_full_name as emp_name,emp_address,dept_id

FROM inserted

return

end

if not exists(select emp_id,dept_id from inserted)

begin

delete FROM aaa.bbb.dbo.emp a INNER JOIN deleted d

ON a.emp_id=d.emp_number and a.dept_id=d.dept_id

return

end

UPDATE a SET a.emp_name=i.emp_full_name

a.emp_Address=i.emp_address

FROM aaa.bbb.dbo.emp a INNER JOIN inserted i

ON a.job_number=i.job_id AND a.event_id=i.event_id

Inserted / deleted table should have the same field name with the original table. Maybe you need to check this out first.

Regards,
kokyan

ambrose_sql Grasshopper Points: 21 More actions · Answer 2

No .unfortunately I have different names on my source and destination tables.Thatswhy i could not even go for replication.

Can anyone tell me how can i update this values into other table

table a on server 1

emp_id dept_id emp_name emp_Address

1 1 hh jj

1 2 uu ii

2 2 jj kk

3 1 ll ee

4 1 mm oo

3 2 ss tt

table a on server 2

emp_id dept_id emp_full_name emp_Address

1 1 hh jj

1 2 uu ii

2 2 jj kk

3 1 ll ee

4 1 mm oo

3 2 ss tt

So if i change

emp_id=2,dept_id=2 record to

2 2 nn sss on table a on server1,how can i update these changes on table a on server2.

like that i will have so many updates on the table a on server1 that should be affected on table a on server2.

Please help me in this regard.

Thanks.

David Scotland-132255 SSCertifiable Points: 5599 More actions · Answer 3

Ambrose

when you update a row in sql server it creates an entry in the inserted and deleted logical tables before the update is carried out. This means that your code would delete the row in the other server before trying to update it.

I would suggest splitting the trigger into separate ones for update, insert and delete. This should be more efficient as well.

By the way you should set no count on at the start of trigger and off at the end

Dave

kokyan SSCommitted Points: 1694 More actions · Answer 4

Avoid create trigger just because you want to populate the updates to the similar table at another server. Use replication feature instead. If the table do not have the same name or datatypes, try to use a view to represent the distributor table. Hope this can help you out and forget about the trigger.

Regards,
kokyan

ambrose_sql Grasshopper Points: 21 More actions · Answer 5

Hi,thanks for ur info.it sounds really good.Can u please tell me in detail how can i do that.i,e view to represent distributor table...

Thanks.