• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 853
  • Last Modified:

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:

CREATE TRIGGER LOGCHANGES1 NO CASCADE BEFORE UPDATE ON dbo.TableA REFERENCING OLD AS O
    FOR EACH ROW
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)

TableA
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.

 
0
garyinmiami2003
Asked:
garyinmiami2003
  • 3
  • 2
2 Solutions
 
Joe WoodhousePrincipal ConsultantCommented:
Which Sybase product and version? They have mutiple database products... `
0
 
garyinmiami2003Author Commented:
version 12.5   32bit open client
0
 
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...
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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?

0
 
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.
0
 
bretCommented:
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
go

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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now