Showing posts with label target. Show all posts
Showing posts with label target. Show all posts

Friday, February 24, 2012

a question about CLR Integration on Trigger coding

I wanna create a trigger using c#. basing on the SQL 2005 Sample, I have
written following code:
[SqlTrigger(Name = "TestForInsert", Target = "[MyUser].[TestTable]",
Event = "FOR INSERT")]
……// c sharp code
when compiling the source code, VS 2005 pops a error which means that it can
not find the object "[MyUser].[TestTable]"
Using the Profiler, I found that when compiling, .Net runtime just add
square brackets at the Target parameter value. In my code, the target is jus
t
[[MyUser].[TestTable]], but [MyUser] should be a schema
how should i do?From the c# documentation I have, you are doing everything correctly. The
code sample I have is
[SqlTrigger(Name = @."EmailAudit", Target = "[dbo].[Users]", Event = "FOR
INSERT, UPDATE, DELETE")]
Perhaps you should attempt to create a trigger on an object owned by the dbo
and then just reference the table name (without the owner). I'd be
interested in hearing how you progress with this.
--
Adam J Warne, MCDBA
"DaemonLin" wrote:

> I wanna create a trigger using c#. basing on the SQL 2005 Sample, I have
> written following code:
> [SqlTrigger(Name = "TestForInsert", Target = "[MyUser].[TestTable]",
> Event = "FOR INSERT")]
> ……// c sharp code
> when compiling the source code, VS 2005 pops a error which means that it c
an
> not find the object "[MyUser].[TestTable]"
> Using the Profiler, I found that when compiling, .Net runtime just add
> square brackets at the Target parameter value. In my code, the target is j
ust
> [[MyUser].[TestTable]], but [MyUser] should be a schema
> how should i do?|||It's been a while since I examined CLR integration so when you say this
->I wanna create a trigger using c#
Can I ask why you wanna.?
"DaemonLin" <DaemonLin@.discussions.microsoft.com> wrote in message news:1129B2AD-515D-4DF5-
B665-131E59939A67@.microsoft.com...
>I wanna create a trigger using c#. basing on the SQL 2005 Sample, I have
> written following code:
> [SqlTrigger(Name = "TestForInsert", Target = "[MyUser].[TestTable]",
> Event = "FOR INSERT")]
> ..// c sharp code
> when compiling the source code, VS 2005 pops a error which means that it c
an
> not find the object "[MyUser].[TestTable]"
> Using the Profiler, I found that when compiling, .Net runtime just add
> square brackets at the Target parameter value. In my code, the target is j
ust
> [[MyUser].[TestTable]], but [MyUser] should be a schema
> how should i do?|||But the table i wanna create trigger on is a member of schema which is not
owned by dbo, how should i do?
"Adam Warne" wrote:
> From the c# documentation I have, you are doing everything correctly. The
> code sample I have is
> [SqlTrigger(Name = @."EmailAudit", Target = "[dbo].[Users]", Event = "FOR
> INSERT, UPDATE, DELETE")]
> Perhaps you should attempt to create a trigger on an object owned by the d
bo
> and then just reference the table name (without the owner). I'd be
> interested in hearing how you progress with this.
> --
> Adam J Warne, MCDBA
>
> "DaemonLin" wrote:
>|||i wrote a sample , the server is SQL 2005 and database is AdventureWorks, th
e
trigger is on the table [HumanResources].[Department], the attribute is:
[Microsoft.SqlServer.Server.SqlTrigger(Name = @."TestTrigger", Target =
"[HumanResources].[Department]", Event = "FOR UPDATE, INSERT, DELETE")]
when i deploy it, IDE hints that
"Cannot find the object "[HumanResources].[Department]" because it does not
exist or you do not have permissions."
using SQL PROFILER and tracing it, the result sent to SQL Server is:
EXEC sp_executesql N'CREATE TRIGGER [TestTrigger] ON
[[HumanResources]].[Department]]] FOR UPDATE, INSERT, DELETE......
The schema HumanResources is owned by user dbo
so, how should i do?
"Adam Warne" wrote:

> From the c# documentation I have, you are doing everything correctly. The
> code sample I have is
> [SqlTrigger(Name = @."EmailAudit", Target = "[dbo].[Users]", Event = "FOR
> INSERT, UPDATE, DELETE")]
> Perhaps you should attempt to create a trigger on an object owned by the d
bo
> and then just reference the table name (without the owner). I'd be
> interested in hearing how you progress with this.
> --
> Adam J Warne, MCDBA

Sunday, February 19, 2012

a query

hello all,

im trying to build a query...

the target of this query is to show the sales man, and the balance of the salesman of credits of a single plan.

the data is stored in the table as following,

RowID,SalesManID,PlanID,Credit,TransactionType

the balanace of of a salesman of a single plan is

the (sum(credit) where Transactiontype = 0 ) - (sum(credit) where transaction = 1 )

ofcourse for every single plan and sales man...

please if u need any question to have more detials just ask

and thank u for ur careTry this:


SELECT PlanID, SalesManID,
SUM(CASE WHEN Transactiontype=0 THEN credit WHEN Transactiontype=1 THEN -credit ELSE 0.0 END) as Balance
From YourTable
Group By PlanID,SalesManID
|||thank you

its a great way

but mmm

it seems that it is not working

as if all of them has transaction type 0, but in the database there is 0 and 1

so is there anything i can do|||Did you get the minus sign in from of the second condition?


SELECT PlanID, SalesManID,
SUM(CASE WHEN Transactiontype=0 THEN credit WHEN Transactiontype=1 THEN-credit ELSE 0.0 END) as Balance
From YourTable
Group By PlanID,SalesManID

If it still doesn't work then try this as a diagnostic. Just run it in query analyzer to see what is going on. You should just see the unaggregated values with some have positive values corresponding to transactiontype=0 and negative for transactiontype=1.
SELECT PlanID, SalesManID,Transactiontype,
CASE WHEN Transactiontype=0 THEN credit WHEN Transactiontype=1 THEN -credit ELSE 0.0 END as Balance
From YourTable
</code>