Simple archive trigger for SYBASE table

If TableA has 3 columns: Col1, Col2, Col3.   and I want to create a row in an archive table (tbl_arch), for the Old part of a change operation:

My guess that it would look something like:

INSERT INTO dbo.tbl_arch  VALUES
(O.Col1, O.Col2, O.Col3, "O")
I think this is what it would look like in db2, what would it look like in sybase?

I need to capture the  date and time stamp (just the system date & time) &  load date.
Can you correct the trigger and complete the definition of the table for system generated fields I am looking for (user, date-time-stamp, load date)

Col1    char(1)
Col2   char(2)
col3  char(3)
DateTime  ?
Load date

 My question:   Review the trigger and correct any syntax, and complete the table. Provide the system variable name in sybase for the time stamp and load date.  Anything close and I'll award the points.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Joe WoodhousePrincipal ConsultantCommented:
Which Sybase product and version? They have mutiple database products... `
garyinmiami2003Author Commented:
version 12.5   32bit open client
Joe WoodhousePrincipal ConsultantCommented:
Will you laugh if I say at least two of the database products from Sybase have a version 12.5? 8-)

I'm guessing you're talking about ASE but there is also a Sybase IQ...
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

garyinmiami2003Author Commented:
I am in a large organization.  I do not have the convenient means to find more about the specific version.  Can you just go with the most likely version?

Joe WoodhousePrincipal ConsultantCommented:
This isn't about the version, but about the product.

This is like asking for syntax in a "Microsoft database" when we don't know if it's Access, FoxPro or MS SQL Server. This isn't a "nice to have", this is "we have to answer the question fully for each possible product and we're lazy here at EE so we'd prefer to only answer one question instead of two or three". 8-)

It matters because in ASE (the most likely answer here) there are no BEFORE triggers at all, and triggers are fired only once per UPDATE, so we have to code it to deal with multiple row updates.
Being an ASE engineer, I would say that is ASE.  But I am biased.  :-)

What do you mean by "load date"?  How would that be any different from the current system date and time?

create trigger mytrigger on mytable for update as
insert  dbo.tbl_arch select Col1, Col2, Col3, getdate() from deleted

To capture information on the user, use suser_id() or suser_name() as an additional value.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.