1

Topic: Does not work the trigger

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) Apr 22 2011 7:23:43 PM Copyright (c) Enterprise Edition Microsoft Corporation (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
Hello, help  to understand why does not work the trigger on update

ALTER TRIGGER [dbo]. [TemraSensorDataNotif_alter]
ON [dbo]. [TemraSensorData_Notification]
AFTER INSERT, update
AS
BEGIN
SET NOCOUNT ON;
if not exists (select * from deleted)
begin
update t
set id_sensor = A.id_sendor
,Sensor_Type = A.Type
from m2. TemraSensorData_Notification t
join (select i.id
,case when ISNULL (i.id_sensor, 0) = 0 then sm.id_sendor else i.id_sensor end as id_sendor
,case when ISNULL (i.id_sensor, 0) = 0 then sm. Type I. Sensor_Type end as Type
from inserted i
left join Reports. Shop_Mesto_Sensor (nolock) sm
on sm. ShopNo=i. ShopNo and sm.sensor_name like RTRIM (ltrim I. Name_sensor))
)A on t.id = A.id
end
insert into _M (id, id_sensor1)
select id, id_sensor
from inserted
END

Essence in that the data gets in m2. TemraSensorData_Notification and then, if id_sensor is null or = 0 then this value undertakes from table Reports. Shop_Mesto_Sensor. I receive on each insertion of values of 2 values in the table _M (1 on insert and 1 on update)
All and works, but there are some records (rare) on which I in the table _M receive only one record on Insert (i.e. the trigger on update does not work). And after, I do update manually

update TemraSensorData_Notification
set id_sensor = id_sensor
where id in (' 8EDB1A4D-D1DD-4421-ABD5-878F7416753C ')

And the data is brought. Why so happens? What did I miss?
Found a similar problem, but its decision does not approach me (if I correctly understood all):
http://www.cyberforum.ru/sql-server/thread1335905.html

2

Re: Does not work the trigger

Sandist;
If it not update then to do update?

if not exists (select * from deleted)

3

Re: Does not work the trigger

TaPaK;
If we interpose to the data, togas yes after we them we update. All is true

4

Re: Does not work the trigger

Sandist wrote:

TaPaK;
If we interpose to the data, togas yes after we them we update. All is true

Well once again re-read that is written

5

Re: Does not work the trigger

TaPaK;
I do not understand...
If the data is interposed (i.e.  deleted empty) we do update
But... The insertion in the table _M does not enter into this piece... It does not depend on this condition

6

Re: Does not work the trigger

Sandist;
I so understand the recursion is included?
m2-? As though basis?
That gives

SELECT * FROM sys.objects WHERE Name = ' TemraSensorData_Notification'

Well and that write the owner do not write

7

Re: Does not work the trigger

TaPaK;
Yes, a recursion... Yes, basis M2, therefore sometimes wrote sometimes is not present (has no like value)
Now I divided the triggers, one delivered on Insert and one on Update separately... Now like all works as it is necessary... But I can not understand why so

8

Re: Does not work the trigger

Sandist;
That returned a script?

9

Re: Does not work the trigger

TaPaK,

10

Re: Does not work the trigger

Sandist;
Test silt also came to a conclusion day that sharing of one trigger on two different (one on insert and one on update) - solved a problem

11

Re: Does not work the trigger

Sandist;
And why all it in procedure do not do?

12

Re: Does not work the trigger

Vladislav Kolosov;
Data gets from indirect sources (if there is a speech about why at once are not installed id_sensor)