DML triggers in sql server Part 43

[@birendrabirbikramshah6409]

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

[@gauravbhardwaj8961]

Same bro am also in grade 9 when this video was uploaded ... And now i am seeing these tutorials i never expect that i will become a programmer 😅 ,thats great somewhere we all are connected to the things which are made for us

[@MistaJones89]

Thank you for this! Very helpful in my introduction to triggers. I've been using SQL server for a few years but never used this functionality before. Very clearly explained!

[@J3ffs23]

Hands down best series to learn programming out there. All videos very concise and explained thoroughly.

[@kyannazion4669]

Wow your explanations are so easy to understand.... thank you so much for all your videos they are amazingly helpful. : ) !

[@Csharp-video-tutorialsBlogspot]

+Kyanna Zion Thanks a million for taking time to give feedback. I am glad you found the videos useful.
Free Dot Net & SQL Server videos for web developers
ua-cam.com/users/kudvenkatplaylists?view=1&sort=dd
If you need DVDs for offline viewing, you can order them using the link below
www.pragimtech.com/Order.aspx
Code Samples, Text Version of the videos & PPTS on my blog
csharp-video-tutorials.blogspot.com
Tips to effectively use our channel
ua-cam.com/video/y780MwhY70s/v-deo.html
Want to receive email alerts, when new videos are uploaded, please subscribe to our channel using the link below
ua-cam.com/users/kudvenkat
Please click the THUMBS UP button below the video, if you think you liked them
Thank you for sharing these links with your friends
Best
Venkat

[@Salariel1224]

My group and i have been watching your videos to help with our class project and it's been so much help. Thank you so much

[@rajivdulal4240]

Replying after 8 years.

[@heathercee25]

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

[@dirtyduck10]

I had difficulty understanding this but you did such an amazing job explaining , highly appreciated

[@9870CrazyMan]

I needed a quick and clear answer on the topic of triggers,
and you delivered perfectly
Thanks!

[@alialfahaid4696]

Helpful to understand, simple to be understood, and easy illustration! Overall, excellent work.

[@Omar.bin.khattab]

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

[@ThuyNguyen-bu9ge]

You are such a wonderful teacher, thank you!

[@sajjadsalam2499]

Your videos are really helpful. Thanks Venkat.

[@sandeepnd]

Thank you, my confusions were cleared in 17 mins :)

[@rakeshjena895]

Sir, You are a gem.. nicely and thoroughly explained. The best teacher of youtube.👍👍👏👏🙏🙏

[@akaluheyi]

easily understandable clarification I have ever seen. Thank you!

[@huwaizatahir6547]

The explanation plus everything is A+ please keep it up

[@KaranShah-u8w]

Wow your explanations are so easy to understand.... thank you so much for all your videos they are amazingly helpful.

[@chocolatelover3770]

I am so so so thsnkful for this - amazing, to the point, and very clearly explained. I learnt more than I would have had I gone to the long, early morning drawn out lectures on this.

[@waseembari2125]

Venkat your videos are too good, Thankyou for your help, Please keep up the good work

[@jimme7763]

Thank you so much Mr.Venkat. Your explanation is quite clear and I could understand once.

[@thuyduongnguyen1231]

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

[@jayamalaraut273]

Thank you so much Sir for all your video's. The way you explains everything is awesome!!

[@dhumal47]

your voice is very natural and lovely keep it up....:)

[@GauravSrivastava2609]

very knowledgeable till video no 43 .....also going forward in this series step by step......, thanks a million Sir, Please upload MSBI( SSRS, SSIS & SSAS) videos

[@ioanabondoc3844]

Thank You so so much for what u are doing for us

[@prasshantchavan3012]

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

[@anubhagupta5408]

NIce explaination. Thank you

[@somecyberpunkstuff3531]

Good job, your vids are complety helpful.

[@webprofessor4439]

Ur a perfect lecturer. Thanks for the good work

[@piyushchaturvedi1489]

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

[@venetiaf1775]

Great Tutorials! I am a beginner and learning sql server. Could you help me understand how the insert and delete triggers can be written when inserting/deleting multiple records.

[@ishankamat9157]

@kudvenkat can you please share the answer for this?

[@mjsblo80]

Senior kud* , u should group your tuts in one biiiiiiig vid tut ( unless u did it already ;) )
excelent and clear ray of knowledge. :)

[@geethakannan7854]

You are awesome teaching , Thank you so much for excellet video sir

[@k333ys2]

Appreciate the Breakdown! Very easy to understand

[@AnimeshSingh7]

Hello Venkat. It's so great to learn from your videos. Your tutorials are not meant only for beginners, but also for intermediate and advanced levels too.
One thing I want to ask in this trigger tutorial. Suppose we have created a trigger for delete on a table which has, say, 50 rows with 'Id' as the primary key. Now, when we delete last 10 records by running query like 'delete from table_name where Id > 40', then there must be 10 records updated in the Audit table right? However, when I am running the same query, I'm getting only 1 row updated in the Audit table with Id = 41. Could you please let me know where am I making the mistake?
Here is the transcript of my queries:
For Audit table:-
create table EmployeeAudit(
ID int,
AuditData nvarchar(100)
)
For Delete trigger:-
create trigger trEmployeesForDelete
on Employees
for delete
as
begin
declare @id int
select @id = d.EmployeeID from deleted d
insert into EmployeeAudit values(
@id, 'An existing record with ID = ' + CAST(@id as nvarchar(5)) + ' has been deleted from Employees table at ' + cast(getdate() as nvarchar(100))
)
end
Running delete query:-
delete from Employees where EmployeeID > 40

[@yrisheet]

Hi Animesh, the deleted table consists of all the rows that are deleted. But in your query on trigger, you are selecting "@id=d.EmployeeID from deleted" which assigns only the first row deleted to @id variable ,leaving rest

[@yrisheet]

Hi Animesh, the deleted table consists of all the rows that are deleted. But in your query on trigger, you are selecting "@id=d.EmployeeID from deleted" which assigns only the first row deleted to @id variable ,leaving rest

[@yrisheet]

Try this
alter trigger tr_tblEmployee_ForDelete
on tblEmployee
for delete
as
begin
insert into tblEmployeeAudit
select 'employee with id'+cast(id as nvarchar(5))+'is deleted' from deleted
end

[@johnwindsor5426]

So nice video. I really like it. I joyed your video every night. I also tell my Students. Ask them learn if "you want to become a professional programmer". You are number one!!!

[@junlopez9774]

Thank you so much, it was well explained. Hope to watch more post from you related to SQL Scripting.

[@syednoumanullahshah4227]

Sir your teaching method is very amazing, it helps us alot in mastering sql server, can you please make a detail video on "SECURING SQL SERVER".
Like logins, server roles, database roles, granting and revoking permissions, etc.
Will be waiting for your kind response.
Thanks in Advance
Regards:
Syed Noumanullah from Pakistan

[@ratatabeats4u]

Hello, Please tell me, when I do "insert from select" how can I add in Audit table values for each row inserted?

[@jag6x2]

This is exactly what I was looking for. Thanks

[@a4advanture755]

thnks man i m learning more and more from your videos

[@taddelepetros8106]

YOU ARE GENIUS!!!!!!

[@taletravel6457]

small correction sir, now we can use inserted and deleted magic tables outside of trigger also, with OUTPUT clause.

[@Csharp-video-tutorialsBlogspot]

Hi RK, thank you very much for answering and helping others. Keep up your good work.

[@mahakhan6745]

Could u tell me which version of this sql server u r using? I am continuously getting an error of column names aren't matching with the magical table attributes inside a trigger.
Thank you!

[@soniyapatel3518]

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

[@mhammednajim8457]

thank you very much
Sorry sir,but you can tell what is the data type of Auditdata.
because when i want to execute the trigger SQL show me error message that the column name or number of supplied values does not match table definition.

[@sumirpv]

insert into tblEmployeeAudit
values(@Id,' an existing........)

[@MrTrojis]

This issue can also be solved by the Id column in the tblEmployeeAudit table being designated as an Identity column

[@skiptomylute5094]

@@MrTrojis Still helping ppl 2 years later. Thanks for the input

[@murahariashok3976]

Brilliant sir......

[@daalwada]

Great explanation sir! Thanks for this wonderful video. :)

[@CoachSergioBB]

Goog video. Venkat in what video serie you talk about creating queries that produce Agreggated Tables for reporting?

[@SanskarAgarwal-r1s]

Amazing Explanation!!

[@ihabfam1059]

EXCELLENT JOB, THANKS

[@SuperAk222]

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

[@ahanamallik340]

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

[@BoxerDogs]

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

[@irfannazari6768]

Thank you for the informative video! :D

[@mahmodmahmod1510]

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

[@bryanmcgee2173]

These videos are a life saver

[@qualityrenov]

I don't know, I'm confused here! probably the syntax you're using is old? to the best of my knowledge, syntax should be:create trigger Name_of_trigger before/or/after ON Name-Of_table for each row begin/*code of what you want the trigger to do here*/endplease advise.as this is also for the dml

[@ginanjarsyarief2954]

So helpful dude i love you

[@lobnamaslih1162]

i'm so greatful thank you so much sir

[@tws46b58]

Fantastic video, thank you so much!

[@himanshubhatt486]

can you please help with the inserting multiple rows using triggers?

[@bountyhead8669]

Very well explained!!!

[@LiahQuinAfrican_Espresso]

you are a life saver :) much thanks !

[@SaivarshiniN]

hi Venkat , may i know what is the use of declare statement in trigger

[@ihabfam1059]

Very good explanation, Thankd

[@kunaltode2691]

As most of the people here is facing the same problem as me that after execution 'Column name or number of supplied values does not match table definition'.Please help us to explain this this

[@mikebenyon4093]

I have had the same problem but seem to have solved it. I don't think Venkat has explained that the Id column in tblEmployee needs to be an identity column. As such you need to re-create tblEmployee and when you do you need to make Id an identity column. I don't believe that you can alter the original table which is a bit annoying. I am learning SQL myself like many others so there may be another way round this but this is the solution that worked for me.

[@mikebenyon4093]

Actually, looking at this further it is the tblEmployeeAudit Id column which needs to be an identity column!

[@oscardelahoya3132]

CREATE TABLE tblEmployeeAudit
(
Id int identity(1,1) primary key,
AuditData nvarchar(1000)
)

[@krzysztofs8535]

GOOD VIDEOS, it was said about ID as IDENTITIY column

[@hariram-uu2qx]

Hi,
this video is for the beginners who want to learn DML TRIGGERS or there is a separate video for that ?

[@sufiyanshaikh812]

how it can be done, if we delete a record by first_name or last_name, I did it and 2 records deleted with surname pandey but in audit table I got to see only 1 entry, how it can be rectified ??
help me

[@amitkumarverma7537]

amazing social work..

[@chetanakakade7494]

Should we not create the "tblEmployeeAudit" table before using it in creating the trigger?

[@tenugolusharath3404]

I have created before trigger creation and I think the table should be created before creating the trigger.

[@irumyousfani7957]

Hello Venkat,
what is the data type of Auditdata. i am receiving errors while creating triggers, it shows message that the column name or number of supplied values does not match table definition.

[@iggzyiggzy]

Hi, I made it of type text, but also had to add @Id in values first to make it work, tho I am using mssql server 2014
values (@ID, 'New employee with Id = ' +
CAST(@ID as nvarchar(5)) +
' is added at ' +
CAST(GETDATE() as nvarchar(20))
)
Btw this is great series for me to learn basics , thx Venkat

[@prashanthidokiburra6700]

create table employee
(
id int primary key identity(1,1),
name varchar(10) not null,
gender char(2) check(gender in('m', 'f')),
salary int default 8000
)
create table emp_audit
(
id int identity(1,1),
audit_data sysname
)
alter trigger tr_employee_insert
on employee
for insert
as
begin
declare @id int
select @id=id from inserted
insert into emp_audit values('new employee with id = ' + cast(@id as nvarchar(5)) + ' is inserted on ' +cast(getdate() as nvarchar(20)))
end
set identity_insert employee on
insert into employee(id, name, gender, salary) values(6, 'Maria', 'F', 8000)
insert into employee(id, name, gender, salary) values(8, 'Sarah', 'F', 22000)
select * from employee
select * from emp_audit

[@prashanthidokiburra6700]

Result:-
ID Audit_Data
1 new employee with id=1 is inserted on Mar 6 2017 10:52PM
2 new employee with id=2 is inserted on Mar 6 2017 10:52PM
3 new employee with id= 5 is inserted on Mar 6 2017 10:54PM
4 new employee with id= 3 is inserted on Mar 6 2017 10:54PM
5 new employee with id= 6 is inserted on Mar 6 2017 10:55PM
6 new employee with id= 4 is inserted on Mar 6 2017 10:55PM
7 new employee with id = 8 is inserted on Mar 6 2017 10:57PM

[@faisalmalik2549]

thanx brother

[@RBLXANIMEEDITZ]

nice!!!

[@Herobrine25251]

i love your videos, it helps me understand things so much better.. thanks , is there anyway i practice more ?

[@revurikiran3753]

hi venket !!
i have a question
what about if we inserted more than 1 row into table how to get thous data into a trigger can you ??

[@samapan420]

Hi Revuri, I have same doubt..did you get any clarification on this?

[@unnamvenkatagowtham7666]

Hello brother thank u very much for the valuable tutorial and can u please tell me that did u created any tblemployeeaudit table separately because I have created the trigger and trying to add the values into the tblemployee table in thought of the values will automatically insert into tblemployeeaudit table but the error is coming like the object with name tblemployeeaudit is not found

[@zessswed]

For some reason when I write the exact trigger as shown at 11:28 and run it I get the following error: "Msg 208, Level 16, State 6, Procedure tr_tblEmployee_ForInsert, Line 1
Invalid object name 'tr_tblEmployee_ForInsert'." Any idea what is gone wrong. I created identical tblEmployee and tblEmployeeAudit tables but it doesn't go through like in the video?

[@RupeshYadavLive]

--This is First Table
CREATE TABLE [dbo].[AuditData](
[ID] [int] IDENTITY(1,1) NOT NULL,
[AuditMessage] [nvarchar](255) NULL
)
-- This is second table
CREATE TABLE [dbo].[tblAuditName](
[ID] [int] NULL,
[Name] [nvarchar](255) NULL,
[Salary] [int] NULL,
[Gender] [nvarchar](255) NULL,
[DepartmentID] [int] NULL
)
-- This is Trigger Code Like special Store procedure
CREATE Trigger [dbo].[tr_tblAuditName_ForInsert]
on [dbo].[tblAuditName]
for Insert
As
Begin
Declare @ID Int
Select @ID= ID From inserted
Insert Into AuditData
Values('New EmpId with ID = ' + cast(@ID as nvarchar(50)) +
' is added at ' + CAST(GetDate() as nvarchar(50)))
End
--- This Insert Statement
Insert Into tblAuditName values(10,'pam',1000,'Mail',5)

[@eq6222]

This only handles one row. Can you please show an example of how this should look to handle multiple rows.

[@sridharat9130]

How to check it for Update ?

[@shubhangiambure8519]

Nicely explain. I have one question.. Can we write one trigger for both Update and Delete??

[@ajayprajapati8684]

+Shubhangi Ambure yes, it's possible u can create one trigger for 3 action insert delete and update
ie.create trigger Employee_triggeron Employees
after UPDATE, INSERT, DELETE
asbeginend

[@ALLCPL]

I have a requirement to mirror insert/update/delete operations from one table to another. For example insert on tableA has to be copied into tableB, update to tableA applied to tableB, and delete from tableA be applied to tableB. It's as simple as that, except tableB has 1 additional column for a constant value, so very simple triggers are needed.
I'm not sure if it is better to write 3 separate triggers, or have one trigger that does all of the operations.

[@rkb8379]

Looks to me that you have added more column values than the required for the table. Please double check whether you have put correct columns and values in the insert statement and execute...that should solve the problem

[@manojgullapalli4302]

Getting this error message while creating insert trigger please let me know how to fix it
''Column name or number of supplied values does not match table definition.
'

[@ericbauwens1642]

Great video and excellent explanation. Thanks very much for sharing.

[@beeah1193]

Thank you for your wonderful explanation on Triggers . But i have a question - > after successfully executing the trigger and deleting the record, My audit table shows empty , that means the "Deleted" has not passed on the values . Can you throw some insight on this please . Or anybody who could solve this ?

[@jyotidatta2126]

I have to create a trigger that throws an error "You are not authorized" when we try to insert any row in a particular table. Can you pls help me create one?

[@muskanahuja4018]

how can i resolve the error saying column name Or number of supplied values does not match the table defination

[@gagansingh3481]

I am getting error after insert values in tblemplyeeaudit ....& then try to execute the command

[@piyushchaturvedi1489]

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

[@vishalbtile]

Your all videos are really awesome and your explanation style is mind blowing !!!! Please carry on help us with the SQL SERVER and BI as well. Would be great help if you start with SSIS and all.

[@marksslonimskis5713]

Thanks man! You helped me a lot!

[@zenboxs8128]

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

[@vincyvarghese430]

i am also getting same error please tell me venkat how to resolve this issue

[@sindhusshortzz]

Can you please provide the complete Query for the above example

[@johnmarkfule1024]

Hi venkat. Is it possible to fire dml trigger when the data is committed.? If so, then how? Thank you hope to hear your answer.

[@robinvalarezo8389]

I have trouble using triggers in stock at products of my data-base. How can I arrange that? any idea? I need help.

[@prathyushasaineni6999]

Thanks for the videos venkat... am following all the videos and am looking for truncate command i could'nt find that please tell me reason is that command is not there in sql server 2005..

[@pushpakgosavi9697]

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

[@LVS311]

Hello Sir, It is possible that a DELETE statement can remove multiple rows at a time (where as an INSERT statement can insert only 1 row at a time). In case of a DELETE statement affecting multiple rows (more than 1 row), How would the FOR trigger for DELETE statement work ? Will it insert the same number of records into audit table (as) the number of affected rows ?

[@kiranchoudhari81]

How can fire DML trigger on two different server. for Ex I have one table on two different servers so how can I create the trigger to udpate the table on another server once data is updated or inserted on table on first server.

[@riteshkothari1647]

Thank you so much for your videos. I had a question: Can I create a trigger on sys.databases table? I want to insert the database id and database name in my view whenever the new database gets created on the sql server instance. Thanks, Ritesh

[@ukkashs444]

Is it necessary to kept id column as a primary key???