how to copy row from one table to another before update on specific column in sybase trigger?

Question

I want to copy a row from one table to other table before an update happens on specific column. this column named StartDateTime_ in my table. can anyone correct this trigger if it is not correct or need optimisation.

CREATE TRIGGER PeriodicHistory_TR_U ON order_db..Periodic
FOR UPDATE
AS IF update(StartDateTime_)
begin
        declare @Identity_;
        declare @Version_;
        DECLARE @Revision_;
        declare @Identifier_;
        declare @CreationTime_;
        declare @CreationUserId_;
        declare @StartDateTime_;        

        SELECT @Identity_= i.Identity_ from inserted i;
        SELECT @Version_= i.Version_ from inserted i;
        SELECT @Identifier_= i.Identifier_ from inserted i;
        SELECT @CreationTime_= i.CreationTime_ from inserted i;
        SELECT @CreationUserId_= i.CreationUserId_ from inserted i;
        SELECT @StartDateTime_= i.StartDateTime_ from inserted i;

        set @Revision_ = @Version_ +1;

 insert into order_db..PeriodicHistory(Identity_,Version_,Revision_,Identifier_,CreationTime_,CreationUserId_,StartDateTime_)

values(@Identity_,@Version_,@Identifier_,@CreationTime_,@CreationUserId_,@StartDateTime_);
end

Answer 1

How about this:

CREATE TRIGGER PeriodicHistory_TR_U ON Periodic
FOR UPDATE
AS IF update(StartDateTime_)
BEGIN
    INSERT INTO     PeriodicHistory(Identity_,Version_,Revision_,Identifier_,CreationTime_,CreationUserId_,StartDateTime_)
    SELECT  d.Identity_,d.Version_,d.Revision_ + 1,d.Identifier_,d.CreationTime_,d.CreationUserId_,d.StartDateTime_
    FROM    deleted d
    JOIN    inserted i
    ON      d.Identity_ = i.Identity_
END

This eliminates the need for temporary variables.

Think about what happens if the update on Periodic affects more than one row. The original trigger won't be able to store multiple values...

Also, my revised trigger updates the version in the history table in the same way as your original trigger. You probably want to update the version in the Periodic table and keep the old value in the history. As it is after ten updates you could end up with: Periodic.Version_ = 1 PeriodicHistory.Version_ = 2