trigger - 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

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-03 : 11:32:24

Hi, I am trying to get a trigger to work but it does not seem to work correctly.

Basically everytime there is an update on tblMain, then the record is set to in-active i.e. Active = 0
and then a same record is created but with Active = 1

Example:

ID Name ProcessID Active
1 test 1 1

Now I am going to edit the above record as follows:
1) Set the Active field to 0 i.e. Active = 0
2) Insert a similar record but with the new ProcessID and Active = 1
i.e
1 test 3 1

Now the trigger should have recorded the above movement of ProcessIDFrom and ProcessIDTo

This is what I have in the trigger but it only puts the ID number:
Do you see what is wrong?...
Thanks

ALTER trigger [dbo].[trgNameUpdate]
on [dbo].[tblMain] for update, insert
as

insert into tblAudits
(
NameID, ProcessFrom, ProcessTo,
CreatedBy,
ModifiedBy
)
select
d.NameID,
d.Process as 'ProcessFrom', i.Process as 'ProcessTo',
i.CreatedBy,
i.ModifiedBy as 'ModifiedBy'
from
deleted as d
inner join inserted as i
on d.NameID = i.NameID

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-03 : 11:39:29

This is what I am doing to update and add a new record...

update
tblmain
set
Active = 0,
ModifiedBy = @ModifiedBy
where
NameID = @NameID

insert into
tblmain
(
[Name],
[Description],
EffectiveFrom,
EffectiveTo,
ProcessID,
Active,
CreatedBy
)
select
[Name],
[Description],
EffectiveFrom,
EffectiveTo,
nullif(@ProcessID, 0),
1,
@ModifiedBy
from
tblMain
where
NameID = @NameID

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-03 : 11:44:32

It seems that because both the update and insert are happening at the same time in one query, it is somehow affecting the trigger working correctly.
Any thoughts please?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 11:45:50

so for the new record you'll have processto as NULL in audit?

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-03 : 11:53:12

It shows what the initial value was and not the new one.
For example if I change ProcessID from 1 to 2 then in the audit table it shows 1 in both ProcessIDFrom and ProcessIDTo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 11:54:43

quote:
Originally posted by arkiboys

It shows what the initial value was and not the new one.
For example if I change ProcessID from 1 to 2 then in the audit table it shows 1 in both ProcessIDFrom and ProcessIDTo

sorry that doesnt make sense. shouldnt audit show the change of state?

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-03 : 11:54:54

May be I have to change the order of the update and insert and somewhere to disable and enable the trigger?

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-03 : 11:57:31

In the tblMain I end up with two records. One with the old ProcessID say 1 and Active = 0
and one new record with ProcessID say = 2 and Active = 1
The audit shows the id of the first record i.e. the one which was updated. it also shows the value of 1 in both ProcessIDFrom and ProcessIDTo

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-03 : 11:59:07

Please note that the problem can be solved if I only do the update on the initial record and do not to the insert. But for business purposes I have to have the insert too.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 12:03:33

quote:
Originally posted by arkiboys

Please note that the problem can be solved if I only do the update on the initial record and do not to the insert. But for business purposes I have to have the insert too.

then shouldnt trigger be only for update?


ALTER trigger [dbo].[trgNameUpdate]
on [dbo].[tblMain] for update
as

insert into tblAudits
(
NameID, ProcessFrom, ProcessTo, 
CreatedBy, 
ModifiedBy
)
select
d.NameID, 
d.Process as 'ProcessFrom', d.Process as 'ProcessTo',
i.CreatedBy, 
i.ModifiedBy as 'ModifiedBy'
from
deleted as d
inner join inserted as i
on d.NameID = i.NameID

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-03 : 12:08:19

The audit still shows processidfrom and processidto fields as the initial value i.e. 1 whereas it should show 1 and 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 12:11:08

wat about this?


ALTER trigger [dbo].[trgNameUpdate]
on [dbo].[tblMain] for update
as

insert into tblAudits
(
NameID, ProcessFrom, ProcessTo, 
CreatedBy, 
ModifiedBy
)
select
d.NameID, 
d.Process as 'ProcessFrom', i.Process as 'ProcessTo',
i.CreatedBy, 
i.ModifiedBy as 'ModifiedBy'
from
deleted as d
inner join inserted as i
on d.NameID = i.NameID

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-03 : 12:22:14

Here is the result -- >
i.Process as 'ProcessFrom', d.Process as 'ProcessTo'
gives: 1
d.Process as 'ProcessFrom', i.Process as 'ProcessTo'
gives: 1
d.Process as 'ProcessFrom', d.Process as 'ProcessTo'
gives: 1
i.Process as 'ProcessFrom', i.Process as 'ProcessTo'
gives: 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 12:29:57

quote:
Originally posted by arkiboys

Here is the result -- >
i.Process as 'ProcessFrom', d.Process as 'ProcessTo'
gives: 1
d.Process as 'ProcessFrom', i.Process as 'ProcessTo'
gives: 1
d.Process as 'ProcessFrom', d.Process as 'ProcessTo'
gives: 1
i.Process as 'ProcessFrom', i.Process as 'ProcessTo'
gives: 1

ok i got it

you're not doing the change in update but you're doing it in next insert. so your trigger should be for the insert


ALTER trigger [dbo].[trgNameUpdate]
on [dbo].[tblMain] for insert
as

insert into tblAudits
(
NameID, ProcessFrom, ProcessTo, 
CreatedBy, 
ModifiedBy
)
select NameID, ProcessFrom, ProcessTo, 
CreatedBy, 
ModifiedBy
from
(
select 
row_number() over (order by m.ModifiedBy desc) as seq,
m.NameID, 
m.Process as 'ProcessFrom', 
i.Process as 'ProcessTo',
i.CreatedBy, 
i.ModifiedBy as 'ModifiedBy'
from
tblmain m
inner join inserted as i
on m.NameID = i.NameID
and m.Process <> i.Process
)t
where seq=1

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-03 : 12:57:00

I do not get anything in the audit now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 12:59:18

quote:
Originally posted by arkiboys

I do not get anything in the audit now.

sorry you should use insert instead of update. see modified solution

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-04 : 03:48:08

This does not seem to work but I have done a workaround by doing the auditing after each update and insert...
Thank you for your time.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 03:49:18

quote:
Originally posted by arkiboys

This does not seem to work but I have done a workaround by doing the auditing after each update and insert...
Thank you for your time.

Why it didnt work? as per your initial explanation it should work.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-04 : 07:06:17

Hello again.
I tried it again... and noticed I was doing something wrong...
All ok now.
Many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 08:16:32

great..