this one was interesting,
at first look you would think you could do
-on before insert trigger
begin
set NEW.record_uid_hash = MD5(NEW.record_uid);
end
and in fact I was able to get that trigger to save, but each run
yielded the same hash value.
so apparently it was hashing an empty value, because the auto-
increment values is not in the NEW yet
also under navicat I found that if you get an error (trigger not
defined) when defining a trigger all the info will remain on the
screen, but the trigger will be gone for that edit session you have to
copy the trigger content into the buffer and then close and then
restart the trigger define process.
That said, I found that I tried LAST_INSERT_ID()
-on before insert trigger
begin
declare vHashValue varchar(60);
set vHashValue = md5(convert(LAST_INSERT_ID(),CHAR));
set NEW.record_uid_hash = vHashValue;
end
but the LAST_INSERT_ID() i just that the last successful insert into
table with autoincrement
so this proved fruitless, and in looking thru the docs there is
apparently now way in a function to get the next auto increment value.
so in looking at what is needed and that is just unique md5 hash to be
used as a cross-reference to the row id and needing to be unique
I came up with this.
-on before insert trigger
begin
set NEW.record_uid_hash = MD5(UUID());
end
doing the md5 on uuid takes the big uuid down to a smaller 32 char md5
Ben
On Jun 2, 2008, at 4:18 PM, Dan Stein wrote:
I am new to using MYSQL usually use MSSQL.
I have a table called record
pk is record_uid (auto increments)
there is a column called record_uid_hash
this is a MD5 of record_uid
I want to create a trigger so when a new record is created the MD5
of the record_uid is entered into the row.
MD5(record_uid)
I can't seem to get the syntax correct.
I use NAVACAT to admin the DB I am getting a message saying no such
trigger.
Any help would be appreciated.
Dan
--
Dan Stein
FileMaker 7 Certified Developer
Digital Software Solutions
799 Evergreen Circle
Telford PA 18969
Land: 215-799-0192
Cell: 610-256-2843
FMP, WiTango, EDI,SQL 2000, MySQL, PHP
[EMAIL PROTECTED]
www.dss-db.com
"The love of one's country is a splendid thing. But why should love
stop at the border? "
Pablo Casals
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
