0

I'm preventing the user from adding the same surname in the list in the code array below. But my goal is to check all the lines using the 'trigger'. As a result: what do I need to do to check all the columns? In summary: Check all columns. If the same value is entered, prevent it from being added.

ALTER TRIGGER trigger_example
ON dbo.information
INSTEAD OF INSERT
AS
DECLARE @surname varchar(30)
select @surname = Person_Job FROM inserted
IF(@surname = 'Enderson')
    BEGIN
        PRINT 'The person with this record already exists in the list.'
    END
ELSE 
    BEGIN
        INSERT INTO personel.dbo.information(Person_id,Person_FirstName,Person_LastName,Person_Salary,Person_Job)
        SELECT Person_id,Person_FirstName,Person_LastName,Person_Salary,Person_Job FROM inserted
    END
GO

enter image description here

  • 2
    (1) Tag with the database you are using. (2) I don't understand what you are trying to do. What is a "code array"? What are "all the columns"? – Gordon Linoff Apr 17 '19 at 20:32
  • Possible duplicate of [How to avoid Duplicate values for INSERT in SQL?](https://stackoverflow.com/questions/17442901/how-to-avoid-duplicate-values-for-insert-in-sql) – Nikhil S Apr 17 '19 at 20:34
  • How is the data being entered? Business rules should be handled at the business layer of an application, not at the data layer. – SS_DBA Apr 17 '19 at 20:37
  • Think about unique indexes/constraints. Though I believe you may do something wrong here. Why shouldn't there be more than one person with the surname of Enderson (just think of a person having parents they usually share the surname with) in the real world? Or even the same given and surname... So why do you want to constrain that? – sticky bit Apr 17 '19 at 20:38
  • @WEI_DBA: Well, if it concerns the data integrity and not having duplicates of some sort may well fall into this category, the database is exactly the right place to implement that. – sticky bit Apr 17 '19 at 20:40
  • mark it as unique!!! – Nikhil S Apr 17 '19 at 20:40
  • For unique indexing of surname column please add see https://www.w3schools.com/sql/sql_create_index.asp – estinamir Apr 17 '19 at 20:41
  • 1
    'anderson' only example. My goal is to prevent the same person from entering again. –  Apr 17 '19 at 20:42
  • @stickybit. Agreed to a point... I don't think a `Trigger` is an efficient way to handle what OP is looking for. – SS_DBA Apr 17 '19 at 20:42
  • 1
    But I want to do this with trigger. –  Apr 17 '19 at 20:43
  • 1
    @WEI_DBA: And I agree in turn that a trigger seems to be the wrong tool for enforcing uniqueness. ;) – sticky bit Apr 17 '19 at 20:44
  • Using a trigger in this situation is equivalent to this I think https://dba.stackexchange.com/a/89629 – estinamir Apr 17 '19 at 21:11

1 Answers1

0

mark the column as unique add up a constraint:

   unique
Nikhil S
  • 3,786
  • 4
  • 18
  • 32