First Trigger Need Help

I apologize in advance for the long post but want to give all the details i can.

I have read everything I can find (BOL, Google'd) about triggers and I am still having problems with writing this.

First off I add rows to "invoicedetails" table by selecting rows from Iventorydatagridview with this code.

Dim dt As DataTable = Me.RCSDataSet.Tables("invoicedetails")

Dim dtRow As DataRow = dt.NewRow()

dtRow("CustomerID") = dgvRow.Cells("CustomerID").Value

dtRow("InvoiceID") = dgvRow.Cells("InvoiceID").Value

dtRow("ItemNumber") = dgvRow.Cells("ItemNo").Value

dtRow("ItemID") = dgvRow.Cells("ItemID").Value

dtRow("Description") = dgvRow.Cells("ItemDescription").Value

dtRow("Units") = dgvRow.Cells("Units").Value

dtRow("Price") = dgvRow.Cells("Price").Value

dt.Rows.Add(dtRow)

Next

That works like a charm.

Next I had to figure out a way to "qty" value in invoicedetails table from "instock" value in inventory table. So, I tried it with a stored procedure.

ALTER PROCEDURE UpdateItemQty1

@.itemid int,

@.Qty INT

AS

DECLARE @.InStock INT;

UPDATE Inventory

SET InStock = Inventory.InStock - @.QTY

FROM Inventory

WHERE (Inventory.ItemID = @.itemid)

SELECT InStock, ItemNo

FROM Inventory

which i called with

' Dim Itemid As Integer = InventoryDataGridView.Item(3, InventoryDataGridView.CurrentRow.Index).Value

' Dim Qty As Integer = InvoiceDetailsDataGridView.Item(6, InvoiceDetailsDataGridView.CurrentRow.Index).Value

' Dim connection As New SqlClient.SqlConnection(My.Settings.RCSConnectionString)

' Dim itemqtyCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("UpdateItemQty1", connection)

' itemqtyCommand.CommandType = CommandType.StoredProcedure

' itemqtyCommand.Parameters.Add(New SqlClient.SqlParameter("@.itemid", SqlDbType.Int)).Value = Itemid

' itemqtyCommand.Parameters.Add(New SqlClient.SqlParameter("@.qty", SqlDbType.Int)).Value = Qty

' connection.Open()

' itemqtyCommand.ExecuteNonQuery()

' connection.Close()

This really gave me trouble. It would only work if i add one row to invoice details then update. If I added more than one row to InvoiceDetails then it would only update the "instock"value of the inventory table for the last row added in invoicedetails.

So then I decided to try a trigger on invoicedetails insert but for the life of me I cant figure out how the thing should be written, I know i have to have a join on the tables but can't figure it out.

I would like the trigger to update the inventory "instock" value based on the inserted "invoiecdetails" rows by invoiceId

I would be very grateful for any help to get over this hurdle.

First, a disclaimer. Keeping values calculated from other values in the database is considered not a good idea. It is too easy for the calculated values to become de-syncronized from the underlying data. Often, a view is created that will provide the calculated values upon need.

A TRIGGER has to be able to handle multiple rows inserted, updated, and/or deleted. Fortunately, there are two virtual tables available only during the operation of the TRIGGER. Those virtual tables are named [inserted] and [deleted]. They will contain both all new values and all removed values. So writing a TRIGGER often entails using those tables. The following will provide you a sense of the form and functionality of a TRIGGER (No guarentees that it works since I don't have the table DDL or sample data.)

CREATE TRIGGER trInvoiceDetails_IU_UpdateQty
ON InvoiceDetails
FOR INSERT, UPDATE
AS
IF @.@.ROWCOUNT = 0
RETURN
IF UPDATE (Units)
UPDATE Inventory
SET Inventory.InStock = ( Inventory.InStock - i.Units )
FROM inserted i
JOIN Inventory
ON i.ItemID = Inventory.ItemID

And then you will need a similar Trigger to add the values back into Inventory on a DELETE.

|||Thank you very much exactly what i was looking for.

firing a trigger on someone's birthday

Hi,
Could somebody help me create a trigger which should be executed when
someone's birthday in months fall in range, like between 18 months and
36 months.
If one falls in that range, the trigger should set a value for that
person to 1.I don't see how or why a trigger would be appropriate for this. Put the
birthdate in your table (and the created / updated date if that's
important) then put the range indicator in a view. You can use the
DATEDIFF function to calculate it:
CASE WHEN
DATEDIFF(MONTH, birthdate, CURRENT_TIMESTAMP)
BETWEEN -18 AND 18
THEN 1 ELSE 0
END
(or use created_date in place of CURRENT_TIMESTAMP if that was what you
meant.)
David Portas
SQL Server MVP
--|||dportas@.gmail.com wrote:
> I don't see how or why a trigger would be appropriate for this. Put the
> birthdate in your table (and the created / updated date if that's
> important) then put the range indicator in a view. You can use the
> DATEDIFF function to calculate it:
> CASE WHEN
> DATEDIFF(MONTH, birthdate, CURRENT_TIMESTAMP)
> BETWEEN -18 AND 18
> THEN 1 ELSE 0
> END
> (or use created_date in place of CURRENT_TIMESTAMP if that was what you
> meant.)
>
Hello David,
The thing is, that the value that should be set to 1 or 0 is in another
table.
I have a view which calculates one age in months. When getting the data
i split up the data in ranges uses a where clause. So i have different
recordsets of subjects where the ages are between 18 and 36, 36 and 52,
etc. What i'm trying to do is when a subject falls in the second range,
like 36 and 52 because he's getting older, i want to set a value in
another table to 1.
I hope i'm making any sense|||Hi Jason !
As the age of people hardly change during the day ;-) a trigger would
be IMHO the wrong solution. Setup a daily / nightly job which updates
the values of the table rather than doing this with a trigger. If the
nightly job is too seldom for you you can schedule the job to run more
often.
HTH, jens Suessmeyer.

FireEvent issues with Reporting Services

I am attempting to use the following command line (and associated script) to trigger an existing subscription within Reporting Services (2000):
command line:

rs.exe -i MyScriptFile.rss -s "MyReportServer/ReportServer"

The input scriptfile (MyScriptFile) contains the lines below where the scheduleID is taken from Reporting Services from an existing 'once only' schedule. The schedule, - when triggered - should fire off a subscription and email recipients with a particular existing report.

Sub Main
Dim rs As New ReportingService()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.FireEvent("TimedSubscription", "87206163-7665-4458-a86c-75b84cf18b2d")
End Sub

Unfortunately, it doesn't work and I almost always get the response:

Unhandled exception:
The request failed with HTTP status 404: Not Found.

If I comment out the line rs.FireEvent( . . . from the script then the command line runs successfully. I can only conclude that there appears to be something wrong with this particular line.

Any ideas anyone?

Kev

Rs.exe takes care of creating the proxy to SSRS and doing authentication, so the first two lines of code (Dim RS & rs.Credentials) aren't actually necessary. The rest of your code looks fine, though.

You're saying that it sometimes does work?

Here are a few things to try:

- Can you check your Subscription ID to make sure it actually exists?

- Does this code work against a Subscription ID directly (in code) from a different (known good) subscription? (For example, see if this sample works: http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.fireevent.aspx)

- FireEvent is the only method you're actually firing against the SOAP API...why don't you include another before you call FireEvent (something simple like ListChildren()) to make sure that the web service can do *any* work on your behalf.

- I've never seen someone specify the server name in their command-line w/o appending http:// first. This mean it won't work, I've just never seen it. Can you try something like this: rs.exe -i myFile.rss -s http://localhost/reportserver -u myDomain\myAccount -p myPassword ? Do this after you remove the first two lines of your code.

Let us know...good luck!

|||Russell, Thanks tremendously for the reply! I spent two whole days on this (without success) trying every variation I could think of.

Firstly, no the code in the original post has never worked; I've not been able to trigger anything. The Subscription ID definitely exists however: I highlighted it and copied it from within the Reporting Services address bar (in the Scheduling page) directly making sure I had it perfect. I did wonder whether it 'was' the intended ScheduleID but the address bar in the browser reads:

http://MyServer/Reports/Pages/Schedule.aspx?ScheduleID=87206163-7665-4458-a86c-75b84cf18b2d

. . . so I am assuming that I really 'do' indeed have the correct ScheduleID it's looking for (87206163-7665-4458-a86c-75b84cf18b2d)? Or is it? I've tried this with double quotes, without quotes, with single quotes all with no luck.

Secondly, the code below works perfectly indicating that the SOAP API will indeed do something for me:

rs.exe -i ListFormatExtensions.rss -s "MyServer/ReportServer"

with the following code in the ListFormatExtensions.rss file:

Sub Main
For Each Ext As Extension In rs.ListExtensions(ExtensionTypeEnum.Render)
Console.WriteLine(Ext.Name)
Next
End Sub

Strangely, ListChildren (as you suggested) didn't work at all? It failed with error BC30451: Name 'ListChildren' is not declared. I would assume that this is something perhaps due to the fact that I am using Reporting Services 2000 and not 2005? The actual failed code I used in the rss file was:

Sub Main
Dim rs As New ReportingService()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.ListChildren()
rs.FireEvent("TimedSubscription", "87206163-7665-4458-a86c-75b84cf18b2d")
End Sub
The server name also works either way I specify it with the ListFormatExtensions test. So both these code snippets work happily and there's no issue with username and password:

rs.exe -i ListFormatExtensions.rss -s "MyServer/ReportServer"
rs.exe -i ListFormatExtensions.rss -s http://MyServer/ReportServer

I tried the option you suggested (without the first two lines of code) which it didn't like:

Sub Main
FireEvent("TimedSubscription", "87206163-7665-4458-a86c-75b84cf18b2d")
End Sub

error BC30451: Name 'FireEvent' is not declared

. . . so I promptly put the first two lines back in.

I am still trying to figure out what you mean by - Does this code work against a Subscription ID directly (in code) from a different (known good) subscription? (For example, see if this sample works: http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.fireevent.aspx)

The web address you gave simply takes me straight to the MSDN definition for FireEvent in Reporting Services 2005.

Kev


|||

Hey Kev --

Even though you can remove "Dim RS", etc. you still use "rs.Whatever" in your code

This worked for me:

1. Created a new subscription off the Adventure Works - Company Sales report: Used the fileshare delivery extension because I don't have an SMTP server handy

2. Put it on a schedule that would not fire until 3 days from now

3. Executed select * from reportserver..subscriptions to get the Subscription ID

4. Threw code below in an RSS file:

Sub Main()

Try
rs.FireEvent("TimedSubscription", "E34849EE-63EE-4BF6-957F-55A9B1132DDB")
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try

End Sub

5. In Report Manager, made sure that my user had "Generate Events" permissions (a system permission)

6. Executed the code with rs.exe -i c:\tryme.rss -s http://servername/reportserver

7. Checked my fileshare, found the report sitting there.

|||

Yes, make sure that you are using the subscription ID, as Russell is, and not the schedule ID that you said you were using.

-Daniel

|||

Guys, thanks for your input!! The problem is now sorted.

Unfortunately, I never gave a thought to the fact that I was running the rs.exe command on my own desktop and not the server on which Reporting Services is actually installed ! Dumb hey! When I put the file TriggerReport.rss on the report server and then ran the command to execute on the report server itself (using the psexec command):

psexec \\myserver rs.exe -i C:\TriggerReport.rss -s "http://myserver/ReportServer"

. . . it triggered the report immediately. The report promptly arrived in my email. I guess the lesson here is to stand back often and ask yourself if you're doing anything obviously stupid before blaming the code.

Kev

|||What Imports do you need for your .NET app if using SSRS 2005? Just thought I'd ask since you guys are the only place I've found really good info on this subject.