Create Trigger in SQL Server

SQL Server create Triggers on INSERT and Update

I think you are mostly correct but are not accessing the INSERTED or DELETED tables correctly.

ALTER TRIGGER [dbo].[T_InsertNewObservation] ON [dbo].[GCUR_OBSERVATION] 
  AFTER INSERT
AS 
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  -- get the last id value of the record inserted or updated
  DECLARE @id INT
  SELECT @id = [ObservationId]
  FROM INSERTED

  -- Insert statements for trigger here
  UPDATE GCUR_OBSERVATION 
  SET GCUR_OBSERVATION.FirstCreatedDate = GETDATE()
  WHERE [ObservationId] = @id 

END

PS. Hopefully this works as I wrote it in notepad and haven't tested it.

How to create trigger for whole database in SQL Server?

Here is answer. I just took all tables from my DB, iterate them and create trigger on every iteration for every table:

DECLARE @Itr INT, @tableName nvarchar(259), @triggerName sysname, @execTrigger nvarchar(max); SET @Itr = 0;
WHILE (SELECT COUNT(*) FROM SYS.TABLES) > @Itr
BEGIN
    SELECT @tableName = QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name),
           @triggerName = 'TRIGGER_' + LEFT(name);
            FROM sys.Tables
            ORDER BY name
            OFFSET @Itr ROWS
            FETCH NEXT 1 ROWS ONLY;
        PRINT @tableName;

    SET @execTrigger = 'CREATE TRIGGER ' + @triggerName + ' ON ' + @tableName +
                       ' FOR UPDATE as SET NOCOUNT ON; UPDATE T SET updated_at = GETDATE() ' + ' FROM ' +
                       @tableName + ' AS T JOIN inserted AS i ON T.id = i.id;';
    PRINT @execTrigger
    EXEC(@execTrigger)
    SET @Itr = @Itr + 1;
end

Use something like that, if you need!

How to put CREATE TRIGGER into TRY-CATCH block with TRANSACTION?

You need to run each create trigger statement in a separate scope/batch (because it has to be the first statement in the batch). So you'll have to escape any quotes in the trigger definitions too:

BEGIN TRY
    BEGIN TRANSACTION;
    exec sp_executesql N'CREATE TRIGGER trTable1_Dates ON dbo.Table1
    AFTER INSERT, UPDATE
    AS
    BEGIN
       SET NOCOUNT ON;
       //An empty string in here has to be '''' to escape the quotes
       SET NOCOUNT OFF;
    END'
    
    exec sp_executesql N'CREATE TRIGGER trTableN_Dates ON dbo.TableN
    AFTER INSERT, UPDATE
    AS
    BEGIN
       SET NOCOUNT ON;
       ...
       SET NOCOUNT OFF;
    END'
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ...
END CATCH;

Transactions are orthogonal to batches and nested scopes, so the transaction covers all activity that occurs inside each EXEC too.

Create Trigger in SQL Server

Databases are set-oriented and triggers are no different. A trigger will fire when a given operation is performed and that operation might affect multiple rows. Thus, the question "Say I want to know the Primary Key of that row" is a misnomer. There could be multiple rows inserted.

SQL Server provides two special tables for AFTER triggers named inserted and deleted which represent the rows that were inserted or deleted by an action and are structured identically to the table being affected. An update trigger might populate both inserted and deleted whereas an insert trigger would only populate the inserted table.

From comments:

but the email recipient will be decided based on a value in a second table, where the foreign key ID is located in the first table (which is the one with trigger

The answer to this question is to use the inserted table (which again, you must assume could have multiple rows) to cycle through the rows and send an email. However, I would recommend against putting email logic in a trigger. Instead, I would recommend putting that logic in a stored procedure and send your email from that.

For reference: Create Trigger

Trigger on insert and update that adds modification date

You need to join the inserted virtual table in the trigger to limit the rows that get updated to those actually changed. Try this:

CREATE TRIGGER ModDate
    ON TableX
    AFTER INSERT, UPDATE
AS
BEGIN
    UPDATE X 
    SET ModifiedDate = GETDATE()
    FROM TableX X
    JOIN inserted i ON X.key = i.key -- change to whatever key identifies 
                                     -- the tuples
END 

Like @ZoharPeled correctly pointed out in a comment below there's really not much point in having the trigger update the date on insert - it would be better to use getdate() as the default value on the column (or even as another column InsertedDate if you want to track when records were initially created) and have the trigger only modify the ModifiedDate column after updates.

See the documentation for more information on the inserted and deleted tables.

How can I create a trigger in SQL Server on insert?

Create Trigger tr_ForInserts_car
ON dbo.car
FOR INSERT
AS
BEGIN 
  SET NOCOUNT ON;

    UPDATE Car
     SET car_make = /*Your Default Value*/
     WHERE car_make IS NULL

     UPDATE Car
     SET car_price = /*Your Default Value*/
     WHERE car_price IS NULL

     UPDATE Car
     SET car_img = /*Your Default Value*/
     WHERE car_img IS NULL
END

Edit

My previous suggestion will update all the rows where corresponding column is null, to update only the column for newly insert rows try the following please

Create Trigger tr_ForInserts_car
ON dbo.car
FOR INSERT
AS
BEGIN 
  SET NOCOUNT ON;

     UPDATE Car
     SET Car.car_make = /*Your Default Value*/
     FROM Car INNER JOIN inserted
     ON Car.PrimaryKey = inserted.PrimaryKey
     WHERE Car.car_make IS NULL

     UPDATE Car
     SET Car.car_price = /*Your Default Value*/
     FROM Car INNER JOIN inserted
     ON Car.PrimaryKey = inserted.PrimaryKey
     WHERE Car.car_price IS NULL

     UPDATE Car
     SET Car.car_img = /*Your Default Value*/
     FROM Car INNER JOIN inserted
     ON Car.PrimaryKey = inserted.PrimaryKey
     WHERE Car.car_img IS NULL

END

---

Related Topics