SQL Server 2008 - Help Writing Simple Insert Trigger

SQL Server 2008 - Help writing simple INSERT Trigger

You want to take advantage of the inserted logical table that is available in the context of a trigger. It matches the schema for the table that is being inserted to and includes the row(s) that will be inserted (in an update trigger you have access to the inserted and deleted logical tables which represent the the new and original data respectively.)

So to insert Employee / Department pairs that do not currently exist you might try something like the following.

CREATE TRIGGER trig_Update_Employee
ON [EmployeeResult]
FOR INSERT
AS
Begin
    Insert into Employee (Name, Department) 
    Select Distinct i.Name, i.Department 
    from Inserted i
    Left Join Employee e
    on i.Name = e.Name and i.Department = e.Department
    where e.Name is null
End

how to work with after insert trigger in sql server 2008

create trigger tri_inserts on a
after insert
as
set nocount on

insert into b (id, name)
    SELECT id, name FROM INSERTED
GO

SQL Server 2008 - using local variables for an INSERT and UPDATE trigger

1. You are assuming that there will only be a single row insert or update.

2. quantity_in_stock FROM products has no predicate - presumably it needs to check the stock level of the inserted productid? If so what is the structure of the products table? (At the moment @stock will be assigned a value from an arbitrary row assuming more than one row in the products table.

3. This will not work under snapshot isolation.

To get around #1 and #2 you would need to JOIN the inserted table onto the products table using productid or whatever and see if any rows exist where inserted.quantity > products.quantity_in_stock

For some ideas about #3 read the discussion here

Creating a simple FOR INSERT trigger on a table

Like Gordon Linoff said, this should be a constraint, but if you really needs to get this done using triggers, this will work:

CREATE TRIGGER dbo.EmployeesTrigger ON dbo.Employees
FOR INSERT
AS
BEGIN
    UPDATE E
    SET E.Country = CASE I.Country
            WHEN 'WA' THEN 'USA'
            ELSE ISNULL(I.Country, 'WA')
        END
    FROM dbo.Employees AS E
    INNER JOIN INSERTED AS I
        ON I.EmployeeID = E.EmployeeID;
END

It will update record once something has been inserted.

• If WA was inserted as a country, it will update it to USA
• If NULL or nothing was inserted, it will default to WA
• On any other case, it will insert whatever was inserted, let's say Canada

SQL SERVER AFTER INSERT TRIGGER TO INSERT NON-ZERO VALUES

Here is one approach, which unpivots your data into a more usable format for inserting then does so:

CREATE TRIGGER yourTrigger ON Client1
AFTER INSERT
AS
BEGIN
INSERT INTO Client2 (Client_ID, Category_Type, Value)
SELECT Client_ID, Category_Type, Value
FROM inserted
CROSS APPLY (VALUES (CATEGORY1,'CATEGORY1'), 
                    (CATEGORY2,'CATEGORY2'), 
                    (CATEGORY3,'CATEGORY3'), 
                    (CATEGORY4,'CATEGORY4'), 
                    (CATEGORY5,'CATEGORY5')) C (Value, Category_Type)
WHERE Value <> 0
END

You mentioned this is transforming data from a staging table.. Consider putting this in a procedure and calling that, instead of having the trigger fire on every insert.

SQL Server 2008 Create Trigger after insert... return id... called stored procedure

In trigger, you can access an table with the name "INSERTED", which will have the newly inserted record details. small example below

CREATE TRIGGER [dbo].[insertNewMarket]   
   ON  [dbo].[markets]  
   AFTER insert  
AS   
BEGIN  
    declare @marketID int  
    SET NOCOUNT ON;  
    SELECT @marketID = ID from inserted  
    exec marketInsert @marketID   
END  
GO  

Simple Update trigger + simple row insert

Ok first, you never under any circumstances design a trigger to update only one row in SQL Server. You design it to update the rows that were inserted, deleted or updated. Triggers operate on batches and you cannot assume that you will never change more than one record in code that hits the database.

You do that by joining to one of two pseudotables that are available only in triggers, inserted or deleted. Inserted contains the new records or the values after an update, delted contains the values for the records deleted or the values before an update occurred.

You could try something like:

ALTER TRIGGER [dbo].[geog_update] ON [dbo].[Site]
FOR UPDATE
AS
SET NOCOUNT ON


UPDATE S
    SET geog = geography::Point(i.[Latitude], i.[Longitude], 4326)
    FROM Site s 
    JOIN Inserted I on s.id = i.id
    WHERE Latitude is not null and Longitude is not null

---

Related Topics