December 23, 2008 at 1:09 am
Hi
i had created one stored procedure which executes by taking certain parameters and i need to create trigger on one table on insert which will execute these stored procedure by taking the parameters which will be the values which has been inserted in the table on which trigger has been created. can i know how do i proceed
December 23, 2008 at 1:26 am
You have to be more specific. What don't you know how to do? How to send a parameter to the procedure? How to write a trigger for insert? How to get the values that were inserted to the table in the trigger?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 23, 2008 at 1:56 am
for triggers, refer bol at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/edeced03-decd-44c3-8c74-2c02f801d3e7.htm
contains complete syntax with examples.
for Procs, refer bol at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/afe3d86d-c9ab-44e4-b74d-4e3dbd9cc58c.htm
this page also has good basic examples.
You can start writing your solution based on the understanding from these and let us know if u're stuck somewhere 🙂
December 23, 2008 at 2:15 am
Best practice is to keep the scope of a trigger as small as possible.
All executed by a trigger is in the same transaction as the original query (insert/update/delete) !
Meaning, if the sproc fails, the trigger fails, the insert/update/delete fails !
OR: use stored procedures to manipulate your data and then just incorporate your wanted proc in that procedure.
OR: just insert the key values and the parameters you need in another "trigger-worker-table" and have a job take care of running the sproc, starting from this trigger-worker-table.
you can even launch the job ad hoc using this method : http://www.sqlservercentral.com/scripts/Miscellaneous/31032/
Don't launch the job from within the trigger using sp_startjob, because that will need security opened up way to much !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 23, 2008 at 3:17 am
there is table1 with coulmnA,coulmnB,coumnC,coulmnd and
there is table2 with coulmn1,coulmn2
and my procedure is
create procedure Detail
as
@cardtape VARCHAR(100),
@device varchar(10)
update table1
set coulmnB=@cardtape,coulmnC=@devid
i have to write trigger on table2 after insert the inserted value of coulmn1 and coulmn2
will the stored procedures @cardtype and @device value
that means it should be like
exec procedure Detail @cardtype,@device
where the value of @cardtype,@device will be inserted values of table2
that means whenever data is inserted in table2 and that value has to be used to process the store procedure
December 23, 2008 at 3:35 am
i had written these code
it give me syntax error
CREATE TRIGGER HeadCount AFTER INSERT ON table2
FOR EACH ROW BEGIN
IF(NEW. coulmn1>2 AND NEW. coulmn1 < 4 ) begin
exec HeadCountDetail(NEW.coulmn1,NEW.coulmn2);
END;
END;
December 23, 2008 at 3:39 am
i had written these code but it gives me syntax error
where headcountdetail is stored procedure and coulmn1 and column2 are coulmns of table2
CREATE TRIGGER HeadCount AFTER INSERT ON table2
FOR EACH ROW BEGIN
IF(NEW. coulmn1>2 AND NEW. coulmn1 < 4 ) begin
exec HeadCountDetail(NEW.coulmn1,NEW.coulmn2);
END;
END;
December 23, 2008 at 4:55 am
santosh.lamane (12/23/2008)
CREATE TRIGGER HeadCount AFTER INSERT ON table2FOR EACH ROW BEGIN
IF(NEW. coulmn1>2 AND NEW. coulmn1 < 4 ) begin
exec HeadCountDetail(NEW.coulmn1,NEW.coulmn2);
END;
END;
SQL doesn't have a row trigger. Triggers fire once per statement, not per row. Hence it's not recommended to have triggers call procedures that have to execute one row at a time. It's slow.
Do you absolutely have to call the proc, or can you move the logic of the proc into the trigger and use the inserted table to process all inserted rows?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 23, 2008 at 4:57 am
ps (12/23/2008)
create trigger trg_MyTrigger on myTableAfter Insert
AS
Declare @cardType as varchar(100)
Declare @devid-2 as varchar(100)
select @cardType=column1, @devid-2=column2 from inserted
exec HeadCountDetail @cardType, @devid-2
What's going to happen there if 3 rows are inserted in a single insert statement?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 23, 2008 at 5:07 am
Hmmm. in that case SP will be fired only for first row. if firing an sp is absolutely required for each inserted row, cursor will be needed. Any alternative to this?
======================
create table t1
(
col1 varchar(100),
col2 varchar(100)
)
create table t2
(
col1 varchar(100),
col2 varchar(100)
)
create proc p1
@c1 varchar(100), @c2 varchar(100)
AS
insert into t2(col1,col2) values( @c1, @c2)
create trigger trg_test on t1
after insert as
declare @col11 varchar(100)
declare @col22 varchar(100)
select @col11=col1, @col22=col2 from inserted
exec p1 @col11, @col22
insert into t1
select 'aa','aa'
union all
select 'bb','bb'
union all
select 'pradeep','pradeep'
select * from t2
Output-
---------
aaaa
============================
December 23, 2008 at 7:15 am
Hmmm. in that case SP will be fired only for first row. if firing an sp is absolutely required for each inserted row, cursor will be needed. Any alternative to this?
Yes, as Gail said, move the logic from the stored procedure into the trigger and use set based logic with the inserted table to do your updating. It might take some extra work to accomplish... but that's what happens when you do things wrong the first time, you need to take extra time to correct them.
There's of course a certain point where the amount of work it would take to correct a bad situation is simply not feasible and you have to maintain bad structure... but you should at least know that's what you're doing... maintaining bad structure because you don't have the time/resources/knowledge to correct it.
December 23, 2008 at 11:02 pm
If that was a plain insert as requested by santosh.lamane, i'd write something like this in the trigger.
=================================
create trigger trg_test on t1
after insert as
insert into t2(col1,col2) select col1,col2 from inserted
==================================
If the target table is little complex and requires updation we can join the table with inserted as the case may be.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply