MSSQL Trigger After update

Question

I have two tables in mssql. When entering data into a table, the STOCK column in the tiger table should be -1, if the STOCK column is equal to 0, then the STATUS should be false. please help.

When you insert it into the TBLACTION dashboard, it will be triggered on the TBLBOOK dashboard

I speak English poorly. I'm sorry.

Answer 1

We have multiple solutions for each requirement. Let's try this:

1. When entering data into a table, the STOCK column in the tiger table should be -1

2. if the STOCK column is equal to 0, then the STATUS should be false

3. When you insert it into the TBLACTION dashboard, it will be triggered on the TBLBOOK 3.a) someone take the book, the column ReturnDate should be NULL on insert; 3.b) someone return the book then you should update the column ReturnDate

   CREATE TRIGGER [dbo].[TBLBOOKInserted] ON [dbo].[TBLBOOK] AFTER INSERT AS BEGIN

   SET NOCOUNT ON;

   UPDATE TBLBOOK SET STOCK = -1 WHERE STOCK IS NULL AND ID IN (SELECT INSERTED.ID FROM INSERTED) END GO

   CREATE TRIGGER [dbo].[TBLBOOKUpdate] ON [dbo].[TBLBOOK] AFTER UPDATE AS BEGIN

   SET NOCOUNT ON;

   UPDATE TBLBOOK SET BOOKSTATUS = 0 WHERE STOCK = 0 AND ID IN (SELECT INSERTED.ID FROM INSERTED) END GO

   CREATE TRIGGER [dbo].[TBLACTIONInsert] ON [dbo].[TBLACTION] AFTER INSERT AS BEGIN

   SET NOCOUNT ON;

   UPDATE TBLBOOK SET STOCK = STOCK-1 WHERE ID IN (SELECT INSERTED.BOOK FROM INSERTED WHERE RETURNDATE IS NULL) END GO

   CREATE TRIGGER [dbo].[TBLACTIONUpdate] ON [dbo].[TBLACTION] AFTER UPDATE AS BEGIN

   SET NOCOUNT ON;

   UPDATE TBLBOOK SET STOCK = STOCK+1 WHERE ID IN (SELECT INSERTED.BOOK FROM INSERTED WHERE RETURNDATE IS NOT NULL) AND ID IN (SELECT DELETED.BOOK FROM DELETED WHERE RETURNDATE IS NULL) END GO