CREATE TRIGGER IF FIELD IS EMPTY DO NOT INSERT

_{Tuesday, March 31, 2015 7:57 AM}

ID LIKE TO CREATE A TRIGGER THAT CHECKS A FIELD FOR 'IS NULL' OR EMPTY AND STOPS A RECORD FROM BEING INSERTED.

IVE TRIED THE FOLLOWING BUT NOT WORKING, I THINK THE ISSUE IS 'BEFROE INSERT' IM NOT TO SURE. MANY THANKS FOR YOUR HELP.

CREATE TRIGGER test
BEFORE INSERT ON tblasset
FOR EACH ROW
  SET NEW.hardwarenumber = 
      CASE WHEN 
       CHAR_LENGTH(NEW.HardwareNumber) = 0 OR
             ELSE NEW.HardwareNumber
      END;

_{Tuesday, March 31, 2015 8:04 AM ✅Answered | 2 votes}

Hi, avoid using triggers when they are unuseful.

In this case you can use a Check Constraint

ALTER TABLE dbo.tblasset ADD CONSTRAINT
    CK_tblasset_HardwareNumber CHECK ([HardwareNumber]<>'' and [HardwareNumber] is not null)
GO

hope will help

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

_{Tuesday, March 31, 2015 8:00 AM}

ID LIKE TO CREATE A TRIGGER THAT CHECKS A FIELD FOR 'IS NULL' OR EMPTY AND STOPS A RECORD FROM BEING INSERTED.

IVE TRIED THE FOLLOWING BUT NOT WORKING, I THINK THE ISSUE IS 'BEFROE INSERT' IM NOT TO SURE. MANY THANKS FOR YOUR HELP.

CREATE TRIGGER test
BEFORE INSERT ON tblasset
FOR EACH ROW
  SET NEW.hardwarenumber = 
      CASE WHEN 
       CHAR_LENGTH(NEW.HardwareNumber) = 0 OR
             ELSE NEW.HardwareNumber
      END;

Hello,

Create a INSTEAD OF INSERT Trigger 

Step by step tutorial here : INSTEAD OF INSERT Triggers

web: www.ronnierahman.com

_{Tuesday, March 31, 2015 8:23 AM}

I would add a filter in INSERT command itself 

INSERT INTO tbl (<columns>) SELECT <columns> FROM tbl WHERE col IS NOT NULL AND col <>''

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Tuesday, March 31, 2015 8:30 AM}

CREATE TRIGGER test
BEFORE INSERT ON tblasset
FOR EACH ROW

That's Oracle (or MySQL) syntax, right? Please note, this is a Forum for Microsoft SQL Server; you should better post your question to a more related forum.

Olaf Helper

[ Blog] [ Xing] [ MVP]

_{Tuesday, March 31, 2015 10:19 AM}

ALTER TABLE dbo.tblasset ADD CONSTRAINT
    CK_tblasset_HardwareNumber CHECK ([HardwareNumber]<>'' and [HardwareNumber] is not null)
GO

Is there away I can implement special characters being inputted on a NVARCHAR datatype using this?special characters like (* , . ,")Many thanks

_{Tuesday, March 31, 2015 1:30 PM}

By the time a trigger is executed the row(s) have already been inserted so it is too late to revent that. A check constraint will force the transaction to rollback. An instead of trigger would allow you to simply not inserte quietly.

Tom G.