August 24, 2020
  • 7:26 am Cách chèn nhạc vào ảnh và video trên Facebook
  • 6:59 am Tóm Tắt The Walking Dead (Xác Sống) | Season 4
  • 6:15 am [Mất gốc Hóa – số 16] – HƯỚNG DẪN CÁCH GỌI TÊN OXIT AXIT – OXIT BAZƠ NHANH THUỘC
  • 5:49 am Cách cài Win từ ổ cứng: cả Win 7/8/8.1 và Win10 (làm mẫu với Win 10)
  • 5:12 am Thủ thuật excel: Cách khóa dữ liệu không cho chỉnh sửa trong excel



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/

admin

RELATED ARTICLES

46 COMMENTS

  1. Syed Waqas Posted on July 10, 2020 at 6:51 am

    Very easy to understand still best in 2020

    Reply
  2. Rene Posted on July 10, 2020 at 6:51 am

    logically there is no way i can use 2 insteqd of trigger, right?

    Reply
  3. Md Mosiur Rahman Posted on July 10, 2020 at 6:51 am

    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

    Reply
  4. Shiffoni Syal Posted on July 10, 2020 at 6:51 am

    Explanations are good but ur energy is low which somehow make bored

    Reply
  5. Ginanjar Syarief Posted on July 10, 2020 at 6:51 am

    Mending belajar dari sini baca modul dosen malah bingung sendiri gafaham

    Reply
  6. Ginanjar Syarief Posted on July 10, 2020 at 6:51 am

    So helpful dude i love you

    Reply
  7. Birendra Bir Bikram Shah Posted on July 10, 2020 at 6:51 am

    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

    Reply
  8. fahad Outlook Posted on July 10, 2020 at 6:51 am

    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………….

    Reply
  9. Eng.Mohammed Salah Posted on July 10, 2020 at 6:51 am

    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

    Reply
  10. shyamprasad atluri Posted on July 10, 2020 at 6:51 am

    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

    Reply
  11. Piyush Chaturvedi Posted on July 10, 2020 at 6:51 am

    please share the Link of "SQL JOIN" concepts if possible

    Reply
  12. Piyush Chaturvedi Posted on July 10, 2020 at 6:51 am

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

    Reply
  13. Amit Chandrakar Posted on July 10, 2020 at 6:51 am

    please start new series on react js and react native if possible.

    Reply
  14. Dogs Are the Best Posted on July 10, 2020 at 6:51 am

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

    Reply
  15. Variety Stuff Posted on July 10, 2020 at 6:51 am

    Thank you

    Reply
  16. Ihab Fam Posted on July 10, 2020 at 6:51 am

    EXCELLENT JOB, THANKS

    Reply
  17. Rohan Rao Posted on July 10, 2020 at 6:51 am

    Great explanation sir! Thanks for this wonderful video. 🙂

    Reply
  18. surya kondoju Posted on July 10, 2020 at 6:51 am

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

    Reply
  19. raza Posted on July 10, 2020 at 6:51 am

    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

    Reply
  20. Ravi Shankar Ladhar Posted on July 10, 2020 at 6:51 am

    thank you, this is really a good tutorial.

    Reply
  21. Ahana Mallik Posted on July 10, 2020 at 6:51 am

    Your Videos are highly helpful. Thanks a lot for easy understanding

    Reply
  22. mahmod mahmod Posted on July 10, 2020 at 6:51 am

    I love your videos so so so so …….much . Thank you

    Reply
  23. Sikandar Ali Posted on July 10, 2020 at 6:51 am

    thnks man i m learning more and more from your videos

    Reply
  24. Henri Fanda Posted on July 10, 2020 at 6:51 am

    excellent tutorial. thanking you.

    Reply
  25. Heather Peltier Posted on July 10, 2020 at 6:51 am

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

    Reply
  26. Jessica Zhang Posted on July 10, 2020 at 6:51 am

    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)

    Reply
  27. Skip ToMyLute Posted on July 10, 2020 at 6:51 am

    2nd to none

    Reply
  28. Saye Posted on July 10, 2020 at 6:51 am

    Auditdata column is nvarchar or date or int?

    Reply
  29. Ioana Bondoc Posted on July 10, 2020 at 6:51 am

    Thank You so so much for what u are doing for us <3 Your videos are amazing!

    Reply
  30. Ihab Fam Posted on July 10, 2020 at 6:51 am

    Very good explanation, Thankd

    Reply
  31. abdul khudhus Posted on July 10, 2020 at 6:51 am

    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

    Reply
  32. Taddele Petros Posted on July 10, 2020 at 6:51 am

    YOU ARE GENIUS!!!!!!

    Reply
  33. Pushpak Gosavi Posted on July 10, 2020 at 6:51 am

    Thank you so much sir for this best video….. thanks a lot

    Reply
  34. Thuy Duong Nguyen Posted on July 10, 2020 at 6:51 am

    amazing! thank you so much I have understand really clearly the code with your instruction

    Reply
  35. Şenol Kurt Posted on July 10, 2020 at 6:51 am

    What if we use 'select @Id=Id from tblemp' instead of 'select @Id=Id from inserted'? Aren't they identical?

    Reply
  36. Huwaiza Tahir Posted on July 10, 2020 at 6:51 am

    The explanation plus everything is A+ please keep it up

    Reply
  37. Ahmed Hafidh Posted on July 10, 2020 at 6:51 am

    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

    Reply
  38. Thuy Nguyen Posted on July 10, 2020 at 6:51 am

    You are such a wonderful teacher, thank you!

    Reply
  39. Akash Nayak Posted on July 10, 2020 at 6:51 am

    Good explanation. Easy to follow. Thank u for this video..

    Reply
  40. Moaath Oliwi Posted on July 10, 2020 at 6:51 am

    thank you <3

    Reply
  41. Prasshant Chavan Posted on July 10, 2020 at 6:51 am

    Thank u for ur guideline…Because of ur video i understand Stored Procedure

    Reply
  42. Lobna Maslih Posted on July 10, 2020 at 6:51 am

    i'm so greatful thank you so much sir

    Reply
  43. Jayce Lila Posted on July 10, 2020 at 6:51 am

    you are fuxxing amazing!

    Reply
  44. zen boxs Posted on July 10, 2020 at 6:51 am

    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

    Reply
  45. patel soniya Posted on July 10, 2020 at 6:51 am

    It's Very HelpFull…….. Explain Very Easy ………..Thank you So Much

    Reply
  46. Souad Bename Posted on July 10, 2020 at 6:51 am

    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;

    Reply
LEAVE A COMMENT