DML triggers in sql server Part 43

46
1



In this video we will learn about triggers in sql serevr. There are 3 types of triggers
1. DML triggers
2. DDL triggers
3. Logon trigger

DML triggers are fired automatically in response to DML events (INSERT, UPDATE & DELETE). DML triggers can be again classified into 2 types
1. After triggers (Sometimes called as FOR triggers)
2. Instead of triggers.

After triggers, fires after the triggering action. The INSERT, UPDATE, and DELETE statements, causes an after trigger to fire after the respective statements complete execution. INSTEAD of triggers, fires instead of the triggering action. The INSERT, UPDATE, and DELETE statements, causes an INSTEAD OF trigger to fire INSTEAD OF the respective statement execution.

Text version of the video

Slides

All SQL Server Text Articles

All SQL Server Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

Nguồn: https://chaoticpharmacology.com/

Xem thêm bài viết khác: https://chaoticpharmacology.com/cong-nghe/

46 COMMENTS

  1. SELECT * from Employees;

    SELECT * from Employees_Audit;

    CREATE TRIGGER tr_Employees_ForInsert

    ON Employees

    FOR INSERT

    AS

    BEGIN

    Declare @Id int

    Select @Id = Id from inserted

    insert into Employees_Audit

    values ('New employee with Id = ' + Cast (@Id as NVARCHAR(5) ) + 'is added at' + cast ( Getdate () as NVARCHAR (20)))

    END;

    I got an error: Column name or number of supplied values does not match table definition. Could please reply. Thanks

  2. Life is Amazing..
    8 years ago when this video was uploaded i was in grade 9.
    i had no idea in future that i will become an programmer…..
    and now here i am watching this video that means somewhere between our lives there will be connection to something in which it seems that our life is exceptional ..
    Just wow <3

  3. hi sir,
    i have one table. it has four column.
    after insert data in first two column.
    I want to do trigger action
    sum w.r.t. column3 where group by column1 and insert into inserted record @ column3
    sum w.r.t. column4 where group by column1 & colum2 and insert into inserted record @ column4.
    same table ……….not other table………….

  4. Perfect ….. thanks for sharing that . some important info for guys … SQL server support only statement-level trigger mean if u try to make multi delete from table [tblEmployee] by this script
    delete from tblEmployee where id in (1,4,6) then ur trigger will only fire once because trigger in sql server is statement-level triggers so be aware if you try to make balances from inserted or deleted rows … solution is avoid multi statement scripts like delete example that we show previously or check inserted or deleted and hold its data inside temp table and loop for that

  5. Create table tblEmployeeAudit
    (
    Id int identity(1,1),
    AuditData nvarchar(100)
    )

    Alter Trigger tr_tblEmployee_ForInsert
    ON tblEmployee
    FOR INSERT
    AS
    BEGIN
    Declare @Id int
    Select @Id = Id from inserted

    Insert into tblEmployeeAudit values ('New Employee With Id = ' + Cast(@Id as nvarchar(5)) + ' is added at '
    + cast(GetDate() as nvarchar(20)))

    End

  6. Thank you for this video and your efforts for all those people specially students, for clearing their concepts…….

  7. At 12:48, I don't understand the purpose of doing ALTER TRIGGER. Also I didn't see ALTER TRIGGER in the DELETE trigger.

  8. How come Evey thing you teach looks so simple at least as long as I am on video… Best man on internet 👍

  9. I created insert trigger and delete trigger on the same table. If insert trigger is working then delete is not working. Both are not working simultaneously. Please help

  10. Thank you for making this video I was having so much trouble understanding DML triggers and your video made it easier to understand

  11. If insert multiple rows at a time, will this trigger still working?? For instance, Insert into tblEmployee values (9, 'Rick', 'Male', 4), (10, 'Jimmy', 'Male', 3)

  12. i tried lot of times venkat it showing error like "Msg 213, Level 16, State 1, Procedure tr_sample_forinsert2, Line 7

    Column name or number of supplied values does not match table definition."please give a solution for this

  13. Create Trigger [dbo].[TriggLog]

    on [dbo].[DatabaseLog]

    for delete, insert

    as
    begin

    declare @id1 int, @id2 int

    select @id1 = [DatabaseLogID] from Inserted

    select @id2 =[DatabaseLogID] from deleted

    insert into [dbo].[tbl_audit] (ID1, id2)

    values (@id1, @id2)

    end
    ——————————————————- Depending on whether ID1 for inserted or ID2 for deleted, I make it much easier.
    select a.ID1, c.[FirstName]+' '+c.[LastName] as insertedName

    from [dbo].[tbl_audit] as a

    Left Join [AdventureWorksDW2017].[dbo].[DimCustomer] as c

    on a.id1 = c.[CustomerKey]

    select a.ID2, c.[FirstName]+' '+c.[LastName] as deletedName

    from [dbo].[tbl_audit] as a

    Left Join [AdventureWorksDW2017].[dbo].[DimCustomer] as c

    on a.id2 = c.[CustomerKey]

    I can store more information in Audit Table because Deleted and INSERTED tables are temporary tables.
    Thank you Venkat you are amazing

  14. I have created the Data audit database just like you but I found the error "Column name or number of supplied values does not match table definition"
    what kind of audit ID is int and what is DataAudit type ??
    so I can not match the table

  15. hello im souad from algeria i m a new developper in oracle apex so i have a prb to create a trigger for insert and update dates
    and this is my script that's i m do it so when i m run this script i have an error means my trigger not valid so please help me and thaink you in advanced
    CREATE OR REPLACE TRIGGER "CONVENTION_T1"
    BEFORE
    insert or update on "CONVENTION"
    for each row
    begin
    declare @DD date
    declare @DF date
    begin
    select @DD=DAT_DEB_CONV from inserted
    select @DF=DAT_FIN_CONV from inserted
    if @DD>@DF
    begin
    raiserror('impossible d'insere cette date ',16,1)
    rollback tran
    end
    end;
    end;
    /
    ALTER TRIGGER "CONVENTION_T1" ENABLE;

LEAVE A REPLY

Please enter your comment!
Please enter your name here