creating a trigger in access .mdb databases?

[reven]

anyone if its possible to create a trigger in a ms access .mdb database file?

im trying to make it so when a new music track is added to the database, a couple of triggers are fired before the insert to first make sure an artist exists in the Artist entity and an Album exists in the Album entity, this way i dont have to do mulitple update commands or multiple checks, just the one insert statement, but i keep getting this error when trying to run the create or replace trigger statement

Syntax error in CREATE TABLE statement.

which is stupid since its not a create table statement..... i thought triggers were in like every db....

[reven]

come on anyone every done this? know if its possible?

[psycik]

I thought you could have an auto macro - I knew you used to be able to, I've just looked but not been able to find anything.

They might have just been for opening the database not adding something to the table.

Sorry not much help.

[reven]

thanks for the input, never really used a macro is all, and a trigger would do exactly what i want, but access doesnt seem to want to create it (and im sure its a standard sql command, and really dont want to have to get people to load mysql or something else).

[bgowland]

Access isn't a 'server' application. Access DBs are just files that you open with ADO/ODBC/whatever providers - you can read/write them, twiddle around with their internals etc. but there's nothing in the background to monitor what's going on.

Triggers and stored procedures are only supported by DB server apps. as far as I can tell.

[reven]

yeah bugger, i thought access had its on server thing internally, i just presumed it would work fine with (since they're just basic sql commands (insert, update etc) just when another insert etc is done).

anyone know if theres a way to do this in access automatically (not via triggers then), i dont really want to have to write 3 different update statements, or use mysql/oracle etc.

[bgowland]

I can't quite get an angle on what the problem is here as I don't know exactly what you're coding. Using ADO, I'd open recordsets to all of the necessary tables and perform SQL commands against those. If an artist/album already exists you don't have to do anything, if either (or both) don't exist you're going to have to create them anyway at some point, regardless of where the processing is handled.

[reven]

i just want to be able to use one sql command to add a music track (ie a music file) to the database, and use a trigger to update the album and artist tables so the artist is added and the album is added (if there arent already there).

it just makes life simplier using one command and leaving it up to the database to ensure consistency.

they way im having to do it now is -
1) read tag info
2) check to see if the artist exists in the artist table
2a) if not add artist
3) check if album exists in album table
3a) if not add album
3b) update artist rating by taking the average of the album ratings for albums by that artist
4) add music track

which is just annoying, with triggers it would just be one sql command.