Friday, September 21, 2012 12:25 PMI 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.
Friday, September 21, 2012 12:34 PM
here is your solution
Ramesh Babu Vavilla MCTS,MSBI
Friday, September 21, 2012 7:26 PMModerator
You could also check-out
SQL Server Setup Team
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.
Saturday, September 22, 2012 6:55 AMAnswerer
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;
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
- Edited by Uri DimantMVP, Editor Saturday, September 22, 2012 6:55 AM
- Proposed As Answer by Kalman TothMicrosoft Community Contributor, Editor Monday, September 24, 2012 5:16 PM
Monday, September 24, 2012 8:33 AM
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 .
TechNet Community Support
Monday, September 24, 2012 10:48 AM
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?
- Edited by Innspiron Monday, September 24, 2012 10:59 AM
Monday, September 24, 2012 5:18 PMAnswerer
Trigger should be your last choice!
Look at Uri's solution.
Trigger usage article:
- Marked As Answer by Innspiron Tuesday, September 25, 2012 4:43 AM
Tuesday, September 25, 2012 12:52 AM
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.
TechNet Community Support
- Edited by Amy PengMicrosoft Contingent Staff Tuesday, September 25, 2012 12:54 AM