sql server trigger

Question

I have a table structure like this:

create table status_master
(
  Name varchar(40)
  status varchar(10)
)

I need to create trigger for status column if the status column value updated value FAIL then the trigger invoke one insert commant like:

insert into temp value('s',s's')

Could you please any one give me tha idea to solve this?

Answer 1

Not sure what you really want to achieve - but in SQL Server, you have two types of triggers:

• AFTER triggers that fire after INSERT, UPDATE, DELETE
• INSTEAD OF triggers which can catch the operation (INSERT, UPDATE, DELETE) and do something instead

SQL Server does not have the BEFORE INSERT/UPDATE/DELETE triggers that other RDBMS have.

You can have any number of AFTER triggers, but only one INSTEAD OF trigger for each operation (INSERT, UPDATE, DELETE).

The more common case is the AFTER trigger, something like:

CREATE TRIGGER trgCheckInsertedValues
ON status_master
AFTER INSERT
AS
BEGIN
  INSERT INTO dbo.temp(field1, field2, field3)
     SELECT i.Name, i.Status
     FROM inserted i
     WHERE i.Status = 'FAIL'
END

Here, I am inspecting the "inserted" pseudo-table which contains all rows inserted into your table, and for each row that contains "status = FAIL", you'd be inserting some fields into a "temp" table.

Again - not sure what you really want in detail - but this would be the rough outline how to do it in SQL Server T-SQL code.

Marc

Answer 2

Trigger in SQL, is used to trigger a query when any action perform in the particular table like insert,delete,update

http://allinworld99.blogspot.com/2015/04/triggers-in-sql.html