title: Is it possible to use a trigger to modify a value before it's inserted or updated?
body: <p>We have several apps putting empty strings into a non-nullable database field which is causing issues with other applications that are expecting a value.  What I'd like to do for now is turn the empty string into a null before an insert or update so that Teradata throws <code>Column 'whatever' is NOT NULL</code> which would result in an exception being thrown in the application.</p>
  
  <p><strong>Edit</strong></p>
  
  <p>I removed my old coneptual SQL which was incorrect and replaced it with new SQL that actually works, but only partially.</p>
  
  <pre><code>Replace trigger mydb.inserttest
  before insert ON mydb.test
  referencing new row as newrow
  for each row
  (
      set newrow.name = case when newrow.name = '' then null else newrow.name end;
  );
  </code></pre>
  
  <p>This appears to replace the empty string with a null before the insert.  However, it also seems to not throw an exception when a UPI is violated.  For example, I have the following table:</p>
  
  <pre><code>create table mydb.test
  (name varchar(20) not null)
  unique primary index (name);
  </code></pre>
  
  <p>I can execute this statement successfully the first time:</p>
  
  <pre><code>insert into mydb.mytable ('joe');
  </code></pre>
  
  <p>It tells me <code>INSERT completed.  1 rows processed</code>.  However, if I run it again, it simply tells me <code>INSERT completed.  0 rows processed</code>.  What you'd normally expect is a <code>Duplicate unique prime key error</code>, but the trigger seems to somehow suppress the exception which causes the calling .NET application to die silently when the UPI constraint is violated.</p>