mssql server management studio create trigger

Question

I am new to the triggers so clear explanation with sample would be awesome. I have 'table1' and I need to add a trigger to it when new row is inserted. In one new inserted row's collumn (cell) of 'table1' there is a string with some select, lets name it select 'Select1'. And now trigger should add new row to 'table2' with values taken from 'table1' and from results from 'select1'. How do I create trigger and how this trigger should look like to achive waht I need? Thanks.

Answer 1

here is your solution

http://www.codeproject.com/Articles/25600/Triggers-Sql-Server

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 2

You could also check-out

CREATE TRIGGER (Transact-SQL)

---

Regards,
Ahmed Ibrahim
SQL Server Setup Team
My Blog
This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
This can be beneficial to other community members reading the thread.

Answer 3

Since SQL Server 2005  you can easily rewrite triggers with OUTPUT clause.

create table itest ( i int identity not null primary key, j int not null unique )
create table #new ( i int not null, j int not null)

insert into itest (j)
output inserted.i, inserted.j into #new
select o.object_id from sys.objects as o

select * from #new
drop table #new, itest;
go

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance

Answer 4

Hi Innspiron,

According to what you said, I wrote down those T-SQL commands, I hope what I wrote down could help you.

create database testdatabase;
use testdatabase;
create table table1
( id int,
  name varchar(20), 
  age int,
  price int);
insert into table1 values(1,'asd',20,3);
insert into table1 values(2,'dsf',23,5);
create table table2
( id int,
  name varchar(20),
  age int);
create trigger testtrigger on table1
after insert
As
declare @id int;
declare @name varchar(20);
declare @age int;
select @id=id from inserted;
select @age=age from inserted;
select @name=name from table1 where price=3;
insert into table2 values(@id,@name,@age);
insert into table1 values(1,'abc',23,10);

Please refer to:
Create Trigger(T-SQL): http://msdn.microsoft.com/en-us/library/ms189799.aspx .

Regards,

Amy Peng

---

Amy Peng

TechNet Community Support

Answer 5

Amy Peng,

asume 'asd' is a string 'select smth from tableN where smth2=4'
so how do I edit this: "select @name=name from table1 where price=3;"? 
Like that: "select @name=smth from tableN where smth2=4;"? And it will work?
What if it will return more than one row?

Answer 6

Trigger should be your last choice!

Look at Uri's solution.

Trigger usage article:

http://www.sqlusa.com/bestpractices/trigger-as-fix-it-all/

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 7

Hi Innspiron,

select @name=smth from tableN where smth2=4

it will work, but if it returns more than one row, only the the last row will be inserted to the table2 in my code.

Regrads,

Amy Peng

---

Amy Peng

TechNet Community Support