Solved-trigger working but updated table is not..

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)

Solved-trigger working but updated table is not..

Author

Topic

tempus
Starting Member

47 Posts

Posted - 2010-06-01 : 03:25:52

Hello again ,

between sales department and prod department i have an issue. i think that this issue can be solved through a trigger. the problem is the following.

while working with the software in the company the sales department enters sales orders , on which the production must produce them if they are not in stock.

the link between the 2 departements is made through a report wich gives information on all orders (order lines to be more precisely)

the order table has many field but in the report i only show a few of them:
client / order / order date / requested delivery date / quantity needed / quantity on stock and a few more non important ones

what i would like is a 2nd table in wich to have all the order lines as they are in the system , and with every modification (new transaction, modified order lines, deleted order lines) saved in the table so i can track the changes of order modification and also to build my report for the production from this 2nd table.

the script looks like this , its not much but since its my first trigger, its a start:

i have created my_table with the following fields :

changedatetime, OR03005 (product code), OR03011_OLD (old quantity), OR03011_NEW (new quantity)

create Trigger tr_Update_RecordSalesOrderChanges
On OR030100
After Insert, Update, Delete As

If update (OR03005)
Begin
Insert INTO my_table(ChangeDateTime,OR03005,OR03011_OLD,OR03011_NEW )
Select getdate(), INSERTED.OR03005, INSERTED.OR03011, DELETED.OR03011
From INSERTED inner join DELETED
on INSERTED.OR03005 = DELETED.OR03005
End

i get some unusual results after tests , results doubled or tripled with just a few miliseconds difference (this might an issue of the software maybe? or a problem at my trigger):
date | product code | old quantity | new quantity
2010-06-01 08:05:14.437 | PF50300 |0.00000000 |1.00000000 (this was a deletion of the product)
2010-06-01 08:08:29.123 | PF10604 |0.00000000 |80.00000000 (this was a deletion of the product)
2010-06-01 08:12:32.717 | PF80200 |1.00000000 |1.00000000 (this was a new product inserted with quantity 1 )
2010-06-01 08:12:33.047 | PF80200 |1.00000000 |1.00000000 (the same as the upper one but with a slighlty different time)
2010-06-01 10:19:53.467 |PF80200 |2.00000000 |1.00000000 (this was a quantity modification from 2 to 1)
2010-06-01 10:19:53.983 |PF80200 |2.00000000 |2.00000000 (this i don't know why its showing )
2010-06-01 10:19:54.547 |PF80200 |2.00000000 |2.00000000 (this i don't know why its showing)

im sure i must insert here order and all the other information that i want in the report also.

if you have any idea please help me, cause im stuck here atm. Also i can provide more information if needed.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-06-01 : 15:00:14

I don't get how that can work at all?

It is only supposed to work when 'update (OR03005)' - when that column changes. But if this column changes, the join 'on INSERTED.OR03005 = DELETED.OR03005' should fail.

tempus
Starting Member

47 Posts

Posted - 2010-06-02 : 01:01:42

Hello Denis,

can you give me a slightly better idea? i can test it and provide feedback.

Thanks in advance!

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-06-02 : 14:58:37

Tempus,

It is possible your software is performing updates in more than one step.

Here are 2 ideas.

1. I am still scrathing my head over your example but maybe try this:

create Trigger tr_Update_RecordSalesOrderChanges
On OR030100
After Update

If update (OR03011)
Begin
Insert INTO my_table(ChangeDateTime,OR03005,OR03011_OLD,OR03011_NEW )
Select getdate(), INSERTED.OR03005, DELETED.OR03011, INSERTED.OR03011
From INSERTED inner join DELETED
on INSERTED.OR03005 = DELETED.OR03005

End

2. I found this on the net and copy pasted:

CREATE TRIGGER Audit 
    ON dbo.Employee 
    FOR INSERT, UPDATE, DELETE 
AS 
    IF (SELECT COUNT(*) FROM inserted) > 0 
    BEGIN 
        IF (SELECT COUNT(*) FROM deleted) > 0 
        BEGIN 
            -- update! 
            INSERT AuditEmployee 
            (EmployeeID, UserName, Operation) 
            SELECT EmployeeID, SUSER_SNAME(), 'U' 
            FROM inserted 
        END 
        ELSE 
        BEGIN 
            -- insert! 
            INSERT AuditEmployee 
            (EmployeeID, UserName, Operation) 
            SELECT EmployeeID, SUSER_SNAME(), 'I' 
            FROM inserted 
        END 
    END 
    ELSE 
    BEGIN 
        -- delete! 
        INSERT AuditEmployee 
        (EmployeeID, UserName, Operation) 
        SELECT EmployeeID, SUSER_SNAME(), 'D' 
        FROM deleted 
    END 
GO

tempus
Starting Member

47 Posts

Posted - 2010-06-09 : 03:54:02

i managed to find the solution. thanks Denis, also thanks to Stepson who helped me a lot.

my example wasnt the best way to describe what i needed. also there was a matter of software and how he treats the updates, inserts and delete's from OR table.

here are the triggers that works for me :

alter Trigger tr_Update_RecordSalesOrderChangesx
On OR030100
after insert
As
if update (OR03005)
begin
Insert INTO BTsalesorders(OR03001, ChangeDateTime,OR03005,OR03011_OLD,OR03011_NEW, client, OR03046, OR03019)
Select INSERTED.OR03001,Getdate(), INSERTED.OR03005, '0' ,INSERTED.OR03011, INSERTED.OR03119, INSERTED.OR03046, INSERTED.OR03019
from INSERTED
End

alter Trigger tr_Update_RecordSalesOrderChanges
On OR030100
after Update ,delete
As
if update (OR03005)
begin
Insert INTO BTsalesorders(OR03001, ChangeDateTime,OR03005,OR03011_OLD,OR03011_NEW, client, OR03046, OR03019)
Select i.OR03001, Getdate(), i.OR03005, b.OR03011 ,i.OR03011, i.OR03119, i.OR03046, i.OR03019
from INSERTED i inner join DELETED b
on i.OR03005 = b.OR03005
WHERE i.OR03011 <> b.OR03011
End

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources