Using CLR with C# within a trgigger

Question

Using CLR with C# within a trgigger

bwhite-825709

Grasshopper

Points: 16
More actions
November 7, 2008 at 8:38 am

#215075

I am new to doing CLR with SQL although I have been doing SQL for many years. I need to create a trigger where C# code is within the trigger then run a simple query to dump the rows in the database. I am just not sure how to get the C# code into the trigger and loaded. Just need some examples to get going.

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

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

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

bwhite (11/7/2008)
I need to create a trigger where C# code is within the trigger then run a simple query to dump the rows in the database.

You don't need C# or CLR in SQL to do this. A standard DML trigger will do it just fine, but you will have to provide a good bit more information before an example could be built. For what operations (INSERT/UPDATE/DELETE) should the trigger fire? What is the table structure? Where do you want the data to be dumped to?

bwhite-825709 Grasshopper Points: 16 More actions · Answer 2

My manager wants it done with C# within a trigger although the other options are known. It would be for an INSERT operation. An example would be to setup a trigger with C# in it where it would update a table. Then call the trigger which would insert a row into the table.

Example:

-- Create table

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].ExampleTrigger') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].ExampleTrigger

GO

CREATE TABLE [dbo].ExampleTrigger (

[EmployeeId] int NOT NULL,

[Name] varchar(30) NOT NULL,

[FEIN] varchar(9) NOT NULL,

[SSN] varchar(9) NOT NULL

CONSTRAINT [ExampleTrigger_PrimaryKey_0] PRIMARY KEY ([EmployeeId]))

GO

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

Get your manager to explain why CLR has to be used for this. TSQL like the following is the fastest method of doing triggers like you describe:

CREATE TABLE dbo.Employee

(

EmployeeId int identity primary key,

Name varchar(30) NOT NULL

)

GO

CREATE TABLE dbo.EmployeeAudit (

AuditID int identity primary key,

EmployeeId int NOT NULL,

Name varchar(30) NOT NULL,

DMLAction varchar(30),

UserName varchar(100) default(system_user),

UserLoginTime datetime default(getdate())

)

GO

CREATE TRIGGER Employee_Insert

ON dbo.Employee

AFTER INSERT

AS

BEGIN

INSERT INTO dbo.EmployeeAudit (EmployeeId, Name, DMLAction)

SELECT EmployeeID, Name, 'Insert'

FROM inserted

END

GO

CREATE TRIGGER Employee_Update

ON dbo.Employee

AFTER UPDATE

AS

BEGIN

INSERT INTO dbo.EmployeeAudit (EmployeeId, Name, DMLAction)

SELECT EmployeeID, Name, 'Updated-To'

FROM inserted

INSERT INTO dbo.EmployeeAudit (EmployeeId, Name, DMLAction)

SELECT EmployeeID, Name, 'Updated-From'

FROM deleted

END

GO

CREATE TRIGGER Employee_Delete

ON dbo.Employee

AFTER DELETE

AS

BEGIN

INSERT INTO dbo.EmployeeAudit (EmployeeId, Name, DMLAction)

SELECT EmployeeID, Name, 'Deleted'

FROM deleted

END

GO

INSERT INTO dbo.Employee (Name)

VALUES ('Jonathan Kehayias')

GO

UPDATE dbo.Employee

SET Name = 'Jon Kehayias'

WHERE EmployeeID = 1

GO

DELETE dbo.Employee

WHERE EmployeeID = 1

GO

SELECT * FROM dbo.EmployeeAudit

GO

DROP TABLE dbo.Employee

GO

DROP TABLE dbo.EmployeeAudit

There is no reason to build a trigger in CLR for what you are trying to do, so examples won't exist for this kind of operation, at least I have never written one, or come across one.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Why does you manager want it done in C#?

[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]

bwhite-825709 Grasshopper Points: 16 More actions · Answer 5

I think because he wants to see how it would work. Wheather or not it actually would be used is a different case as I agree there are better ways to do. So, could the C# code be built into an assembly and then called by a trigger? I have not really done anything with assemblies before. Thanks.

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

bwhite (11/7/2008)
I think because he wants to see how it would work. Wheather or not it actually would be used is a different case as I agree there are better ways to do. So, could the C# code be built into an assembly and then called by a trigger? I have not really done anything with assemblies before. Thanks.

You don't seem to understand how CLR is integrated in SQL by your questions. I'd recommend that you read the following BOL articel before trying to do anything with CLR in SQL.

http://msdn.microsoft.com/en-us/library/ms345136(SQL.90).aspx

You build a CLR Trigger on a table and deploy it. From there it works as a trigger in SQL works. When the appropriate change happens on the table, the trigger fires, and the code in the CLR Trigger is what executes. You don't recall the CLR code.

Could it be done, absolutely, but it isn't recommended that you use CLR for trivial items that TSQL already does well.

bwhite-825709 Grasshopper Points: 16 More actions · Answer 7

Thanks for the help. I have it all coded and working fine.