CLR Triggers

Question

CLR Triggers

haroonrashed786

SSC Rookie

Points: 38
More actions
June 11, 2009 at 5:54 am

#206240

Hi all..
Iam new to clr programming.i have created storredprocedures using clr programming,I have created triggers also.iam using the trigger in 2 cases .1.Insert 2.Update.when iam updating or Inserting .i need to keep track of all changes in other databade table.when iam updating or inserting row ,i need to get the ID Collumn of table.it is working fine with SPs,when include triggers iam getting wrong .insted of getting value of ID collumn of original table iam getting the value of Triggers table collumn ID, I couldn't understand wt is happening..
here is the source code
public partial class Triggers
{
// Enter existing table or view for the target and uncomment the attribute line
[Microsoft.SqlServer.Server.SqlTrigger(Name = "trigTbAppointment", Target = "tbTABLENAME", Event = "AFTER INSERT, UPDATE")]
public static void trigTbAppointment()
{
SqlTriggerContext Context = SqlContext.TriggerContext;
SqlPipe Pipe = SqlContext.Pipe;
// Retrieve the connection that the trigger is using
using (SqlConnection con = new SqlConnection(@"context connection=true"))
{
try
{
con.Open();
SqlCommand com = new SqlCommand(@"INSERT INTO [DBNAME].dbo.[tbTABLENAME]
(TESTID,
NAME
)
SELECT ID,
Name,
FROM INSERTED;", con);
//com.ExecuteNonQuery();
Pipe.ExecuteAndSend(com);
}
catch (SqlException exc)
{
throw exc;
}
finally
{
con.Close();
}
}
}
}
Help me,it would be a grt help
Thanq

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

haroonrashed786 (6/11/2009)
...when iam updating or inserting row ,i need to get the ID Collumn of table.it is working fine with SPs,when include triggers iam getting wrong .insted of getting value of ID collumn of original table iam getting the value of Triggers table collumn ID

This does not make sense. In a trigger, the "original table" and the "trigger table" are the same thing. So thier IDs are the same also. Please explain. Perhaps an example would help.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

haroonrashed786 SSC Rookie Points: 38 More actions · Answer 2

I mean to say that ,i need to be store the table row data in other table when ever row is updating or Inserting,ie i have to keep track of data in other database table .

Original table is "Actual table"

Trigger table is "which we are using to track tha data"-Duplicate of original data.

I hope u will be clear

Thanq

Young

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

You don't need CLR to do this. TSQL is the best way to accomplish this task just using standard DML triggers.

http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/a3c372a4-d8f0-47b5-a45b-a7529e6eaa81/

haroonrashed786 SSC Rookie Points: 38 More actions · Answer 4

but..i have to use CLR triggers only..,still iam getting same error

Thanks

Johan Bijnens SSC Guru Points: 135382 More actions · Answer 5

doing that with any clr proc will be at least 95% overhead !!

e.g.

create trigger trIU_mytable

as

begin

insert into myaudit_table

select 'I' as operation, *

from inserted

union all

select 'U' as operation

, *

from deleted

end

Doing this in a clr module would be like taking a care fetch your email :hehe:

CLR does indeed have its purpose, but the general rule is:

- do it in TSQL

- if tsql doesn't perform well enough, try it with clr but TEST IT and compare it all the way !

Keep your triggers as small (short) as possible !

Another this to keep in mind: If you don't need it in transaction scope, make it asynchrone ! (if your trigger fails, your transaction fails !)

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 6

haroonrashed786 (6/20/2009)
but..i have to use CLR triggers only..,

Why?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

Florian Reischl (6/20/2009)
haroonrashed786 (6/20/2009)
but..i have to use CLR triggers only..,
Why?

Exactly my thinking... This requirement makes absolutely no sense. It's like saying you have to use a hammer to drive a screw into a board. It might get the job done, but a screwdriver makes a whole lot more sense.

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 8

haroonrashed786 (6/20/2009)
but..i have to use CLR triggers only

As previous suggested, you really need to tell us why you are constrained to using only CLR triggers. Even CLR experts like Jonathan Kehayias are telling you this is a bad idea. BUT, you may have a valid reason for doing this and before folks can help, they need a wee bit more information... like why the constraint of using only CLR triggers is essential.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)