trigger for insert or update after checking relationship

Question

I have this 3 tables:

And i need to build a trigger that: A date ("encontro") can only works when theres a friendship ("amizade") between 2 profiles ("perfis").

I've created this trigger but i feel lost.. HELP ME

CREATE TRIGGER relaçoes_after_insert
ON encontros
INSTEAD OF insert       -
as
begin

    declare @idperfilA int;
    declare @idperfilB int;
    declare @data datetime;
    declare @count int;

    declare cursor_1 cursor for select * from inserted;
    open cursor_1;
    fetch next from cursor_1 into @idperfilA, @idperfilB, @data;

    WHILE @@FETCH_STATUS = 0
    BEGIN

        if exists( select * from inserted i, amizade a
               where i.IDPERFILA = a.IDPERFILA and i.IDPERFILB = a.IDPERFILB and GETDATE() > DATA)
        RAISERROR('there isnt friendship', 16, 10);
    else
        insert into ENCONTROS select * from inserted;
end;

        fetch next from cursor_1 into @idperfilA, @idperfilB, @data;

    END
    close cursor_1;
    deallocate cursor_1;

Will the `IDPERFILA` and `IDPERFILB` values on the inserted record always match the corresponding A or B values in the `AMIZADE` table? Or could you have a situation where `@idperfilA` = `AMIZADE.IDPERFILB` and `@idperfilB` = `AMIZADE.IDPERFILA`? — 3N1GM4, Dec 09 '16 at 13:34
I might be a bit confused, but `if exists ...where i.idperfila = a.idperfila and i.idperfilb = a.idperfilb and getdate() > data` wouldn't that mean there __is__ a friendship? -- Also, you don't need a cursor for this. — SqlZim, Dec 09 '16 at 13:34
@SqlZim I assumed the cursor was to handle multi-row inserts (like in [this question](http://stackoverflow.com/questions/2178889/sql-server-a-trigger-to-work-on-multiple-row-inserts)) - is it still not necessary even if this might be the case? — 3N1GM4, Dec 09 '16 at 13:35
[@Raquel Fartaria Salvador](http://stackoverflow.com/users/7186252/raquel-fartaria-salvador) - why have you included `GETDATE() > DATA` in the `SELECT` query within your `IF EXISTS`? Do you only want to prevent the `INSERT` if the "friendship" has a date in the past? Also, you don't detail what result you are getting, only that you "feel lost" - perhaps you can describe exactly what is going wrong? — 3N1GM4, Dec 09 '16 at 13:37
It should probably be `if not exists`. @3N1GM4, the cursor or a temp table needs to be used to be able to raise the error with multi-row inserts. To avoid the cursor she could insert into a temp table the left join of inserted and amizade, check for nulls, raise an error if there are any, otherwise insert into encontros. I suspect the temp table approach might be slightly faster. — SMM, Dec 09 '16 at 13:51
Raquel, you should avoid using implicit joins (using `,`) and instead use explicit joins using the `join` operator. — SMM, Dec 09 '16 at 13:53
@3N1GM4 if exists some friendship with a date after today (for example) it is an error, so the friendship doesn't exist. But i dont know if it matters at this point. IDPERFILA and IDPERFILB will match A and B at amizade table, but i need to make sure that they were not the same — Raquel Fartaria Salvador, Dec 10 '16 at 14:49

SqlZim · Answer 1 · 2016-12-10T15:53:27.683

I think the better answer would be to not create use a trigger for this at all. Instead I would create and enforce a foreign key constraint between encontros and amizade.

As far as I can tell, this will result in doing what you want without having to write your own code to try and recreate behavior provided by the database. It also makes it much easier to understand from a database design point of view.

alter table dbo.encontros
  add constraint fk_amizade__encontros 
  foreign key (idperflia, idperflib) references dbo.amizade (idperflia, idperflib) 
  /* optional 
     on delete { no action | cascade | set null | set default } -- pick one, usual defualt is: no action
     on update { no action | cascade | set null | set default } -- pick one, usual defualt is: no action 
 --*/*
 ;

More about table constraints.

NO ACTION The SQL Server Database Engine raises an error and the delete action on the row in the parent table is rolled back.

CASCADE Corresponding rows are deleted from the referencing table if that row is deleted from the parent table.

SET NULL All the values that make up the foreign key are set to NULL when the corresponding row in the parent table is deleted. For this constraint to execute, the foreign key columns must be nullable.

SET DEFAULT All the values that comprise the foreign key are set to their default values when the corresponding row in the parent table is deleted. For this constraint to execute, all foreign key columns must have default definitions. If a column is nullable and there is no explicit default value set, NULL becomes the implicit default value of the column.

Based on your reply to @3N1GM4:

@3N1GM4 if exists some friendship with a date after today (for example) it is an error, so the friendship doesnt exist. But i dont know if it matters at this point. IDPERFILA and IDPERFILB will match A and B at amizade table, but i need to make sure that they were not the same

You could create a check constraint on amizade that will prevent rows with invalid dates from being inserted into the table.

alter table dbo.amizade
  add constraint chk_data_lt_getdate ([data] < get_date());

More about check constraints; more examples from Gregory Larson.

original answer:

I'm still waiting on some clarification on the question, but one of the versions in this should be on the right path:

create trigger relaçoes_after_insert
on encontros
instead of insert
as
begin

/* To abort when any row doesn't have a matching friendship */
if not exists (
  select 1 
    from inserted i 
    where exists (
      select 1 
      from amizade a 
      where a.idperfila = i.idperfila 
        and a.idperfilb = i.idperfilb 
         and getdate() > data  /* not sure what this part does */
        /* as @3N1GM4 pointed out, 
         if the position doesn't matter between idperflia and idperflib then:
      where (i.idperfila = a.idperfila and i.idperfilb = a.idperfilb) 
         or (i.idperfila = a.idperfilb and i.idperfilb = a.idperfila)
        */
          )
  begin;
    raiserror('there isnt friendship', 16, 10);
    else 
    insert into encontros 
      select * from inserted;
  end;
end;

/* To insert all rows that have a matching friendship, you could use this instead */
  insert into encontros 
    select i.* 
      from inserted i
      where exists (
        select 1 
        from amizade a 
        where a.idperfila = i.idperfila 
          and a.idperfilb = i.idperfilb 
           and getdate() > data  /* not sure what this part does */
          /* as @3N1GM4 pointed out, 
           if the position doesn't matter between idperflia and idperflib then:
        where (i.idperfila = a.idperfila and i.idperfilb = a.idperfilb) 
           or (i.idperfila = a.idperfilb and i.idperfilb = a.idperfila)
          */
            )
end;

The only potential issue I see with using an inner join instead of exists for the second option (inserting rows that have a friendship and ignoring ones that don't) is if there could ever be an issue where (i.idperfila = a.idperfila and i.idperfilb = a.idperfilb) or (i.idperfila = a.idperfilb and i.idperfilb = a.idperfila) would return duplicates of the inserted rows from each condition returning a match.

Thanks a lot! but i didn't quite understod, i need to insert something before the trigger or the trigger will try to insert correct and incorrect records at the same time? — Raquel Fartaria Salvador, Dec 10 '16 at 14:55

trigger for insert or update after checking relationship

1 Answers1