on trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

on trigger

Author

Topic

vinodalagandhula
Starting Member

7 Posts

Posted - 2011-11-23 : 16:58:03

i was given a question in one of the interview's i need solution for that

a)

create 2 tables tableA and tableB in a database

b)
create a trigger on tableA for after insert and update

when the trigger fires following action should take place.

tableB should be updated with runningId,action( whether its insert or update), datetimestamp

please let me know solution if any one has come across this ..

thanks

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-11-24 : 04:02:38

Theres a few ways to do this, but if you couldn't answer it, you need to learn more about SQL. Look up OUTPUT, that is the easiest way to write the values out.

Also look up INSERTED and DELETED.

vikki.seth
Yak Posting Veteran

66 Posts

Posted - 2011-11-24 : 07:38:11

Please look into
http://msdn.microsoft.com/en-us/library/ms191300.aspx

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-24 : 08:05:16

Think these contain everything you need
http://www.nigelrivett.net/SQLTriggers/Triggers_1_A_beginners_guide.html
http://www.nigelrivett.net/SQLTriggers/Triggers_2_Creating_Audit_Trails.html

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vinodalagandhula
Starting Member

7 Posts

Posted - 2011-11-24 : 08:05:32

I have worked out the solution

create table table1
(
id int,
name varchar(10)
)
create table table2
(
runningID int,
action varchar(10),
datetimestamp datetime
)

create trigger vinodtrigger on table1 after insert,update,delete
as
begin
DECLARE @Action as char(10)
DECLARE @Count as int
SET @Action = 'INSERT' -- Set Action to 'I'nsert by default.
SELECT @Count = COUNT(*) FROM DELETED
if @Count > 0
BEGIN
SET @Action = 'DELETE' -- Set Action to 'D'eleted.
SELECT @Count = COUNT(*) FROM INSERTED
IF @Count > 0
SET @Action = 'UPDATE' -- Set Action to 'U'pdated.
END

insert into table2(action,datetimestamp) values(@action,getdate())
end

vinodalagandhula
Starting Member

7 Posts

Posted - 2011-11-24 : 08:11:05

thank you guys for the help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources