The best way to check if a value in a table has changed when writing to a database

database-designinsertoracleselect

Let me provide you with an example. Let's say I have an html_tag with two columns, tag_type and tag_value

For example, let's say I have a record with the value "link" under thetag_type field and "https://dba.stackexchange.com" under the tag_value field.

Now, how would I approach this for auditing purposes? I want to only INSERT into this table when one of the tag_value fields corresponding to a tag_type has changed, because it would not make sense to add redundant records to this table.

Is this a database design problem I'm having, or is there advanced SQL I'm unaware of that would accomplish this? Or, rather, would the Java I am getting these values from initially be the place to handle such a thing? Example of how I would do this in Java (psuedo code):

var newTag = extract the tag's value from my webpage;

var tag_value = SELECT tag_value from html_tag
WHERE tag_type = 'link'

if (new_tag != tag_value) then (INSERT INTO html_tag)...

Should I handle this at the database level, or java level, like directly above?

Best Answer

If you need combination of (tag_value,tag_type) to be unique , the only guaranteed way to do it is to add unique constraint or unique index, e.g. alter table html_tag add constraint UQ_HTML_TAG_COMPOSITE UNIQUE(tag_value, tag_type) . In addition you can modify INSERT statement so it will not insert a record if it already exists. Something like 

INSERT INTO html_tag(tag_value,tag_type) 
SELECT 'dba.stackexchange' as tag_value, 'link' as tag_type FROM DUAL 
WHERE NOT EXISTS 
( 
  SELECT NULL FROM  html_tag a WHERE a.tag_value = 'dba.stackexchange' 
  and a.tag_type = 'link'
)

will greatly reduce (not 100% though due to concurrency) chances of inserting duplicate records. You may also try using MERGE. However, no matter how you insert the record, the only way to completely avoid duplicates is to enforce it on DB level by adding unique constraint /index.

Related Question