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

What you're looking for is an INSTEAD OF INSERT, UPDATE trigger. Within your trigger you attempt the insert or update yourself inside a try-catch. If it errors out then you insert those values into your other table (assuming it's a logging table of some sort).

Answer 2

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 3

Assuming what you mean is, should the status's new value be FAIL, then what about this:

triggers reference the new record row as 'inserted' and the old one as 'deleted'

CREATE TRIGGER trgCheckInsertedValues ON status_master AFTER INSERT AS

BEGIN

if inserted.status = 'FAIL' INSERT INTO dbo.temp(field1, field2, field3)
SELECT i.Name, i.Status, 'anything' FROM inserted i