SQL Trigger After Insert Update Another Table with Conditions

Sql Trigger After Insert Update another table with conditions

What you're looking for here is the inserted table. Every time you issue an UPDATE statement, SQL Server generates two virtual tables called inserted and deleted that store information on the data modifications you're making. These tables are accessible from your trigger. For more information, see here: https://msdn.microsoft.com/en-us/library/ms191300.aspx

You can use inserted to get the IDs you're looking for. So, instead of:

WHERE ClaimCustomerId=1

you can use:

WHERE ClaimCustomerId=inserted.ClaimCustomerId

Mysql trigger after update insert into another table with condition

LIKE cannot be used in a simple comparison it's only supported in a WHERE clause. Don't forget to change the delimiter as well.

DROP TRIGGER IF EXISTS  `student_approve`;
DELIMITER //
CREATE TRIGGER `student_approve` AFTER UPDATE ON `student_info` 
FOR EACH ROW 
BEGIN
   IF NEW.student_approval = '1' THEN
     INSERT INTO `notifications` (user_to_notify,who_fired_event,noti_event_id) VALUES(NEW.registered_by,1,2);
   END IF;
END//
DELIMITER ;

Would fix the syntax error but I of course we cannot tell if the result is what you want.

How to insert data into another table when the condition is met with trigger

SQL Server triggers works with sets not single register i did some small changes in your trigger

create TRIGGER dbo.usp_PersonConfirmed ON dbo.personRegistration
AFTER UPDATE 
AS
  BEGIN

   -- create person if not exists
   INSERT INTO dbo.person (firstName, lastName) 
   SELECT firstName, lastName
   FROM dbo.personRegistration p
   where not exists(select * from dbo.Person where firstName = p.firstName 
    and lastName = p.lastName)

   -- create orgonization unit if person dont exist and confirmed is Y
   INSERT INTO dbo.person_organizationalUnit (personId, organizationalUnitId)
   SELECT i.personId, I.organizationalUnitId
   FROM Inserted AS I
   where not exists(select * from dbo.person_organizationalUnit where 
      personId = i.personId)
      and confirmed  = 'Y';

   -- update orgonization unit if person exist and confirmed is Y
   update pou set organizationalUnitId = I.organizationalUnitId
   from dbo.person_organizationalUnit pou
     inner join Inserted AS I on i.personID = pou.personId
   where  i.confirmed  = 'Y';
END

Update table on condition from another table Trigger

I would not store the value rating in your table as it can be derived from the other table's data when it is required. And why are you using float for the count?

Create a VIEW which counts the rows in Starsin, based on your condition role = 'main' and then you have no need for the trigger and the count will always be up to date when new rows are added or removed.

With the trigger solution you also have to take account of row deletion to decrement the count.

EDIT: From the comment you made, here's a sample trigger (although the syntax may be wrong as I mainly work with SQL Server). I'm assuming the name field in the MovieStar table is a primary key and therefore unique.

CREATE TRIGGER UpdateRating AFTER INSERT ON StarsIn
FOR EACH ROW
BEGIN
    UPDATE MovieStar SET rating =
        (SELECT rating FROM MovieStar WHERE name = NEW.starname) + 1
    FROM MovieStar INNER JOIN NEW ON MoveStar.name = NEW.starname
    WHERE NEW.role = 'main'
END

I'm not familiar with MySQL, I work with SQL Server, where triggers need to be set based. I'm guessing the FOR EACH ROW part of the trigger means the statements are executed for each row inserted, but I may be wrong about that.

---

Related Topics