CREATE TRIGGER [UPDATEEGBKMUTREBATERECORDS] ON [dbo].[GBKMUT]
after INSERT
AS
begin
DECLARE@Sum Float
SELECT@Sum = SUM(bdr_hfl)
FROMinserted
WHEREfreefield3 = 'Rebate'
I need @Sum to reset to zero if the ord_no changes. I'm inserting discount records that need to subtract from another account's amount.
As records are inserted for one ord_no and Inv_no I need to sum bdr_hfl field. When the ord_no and Inv_no change, I need to subtract that from the amount in the bdr_hfl where the account number is 1040 and the inserted records ord_no and Inv_no match the ord_no and Inv_no where the account is 1040. Then I need the @sum to reset to zero and start summing again.
I know how to reset the page numbers with each group, but how do you reset the total page number within each group.
EX. Code for page of total pages
="Page " & Globals.PageNumber & " of " & Globals.TotalPages
EX. Code to reset within a group Custom Code: Shared offset as Integer Shared currentgroup as object
Public Function GetGroupPageNumber(group as Object, pagenumber as Integer) as Object If not (group = currentgroup) offset = pagenumber - 1 currentgroup= group end if return pagenumber - offset end function
I am trying to calculate the the running total but also tried to reset to reset to zero based on a value of a column.
here I am trying to calculate the value of CalcVal column based on column Flag value...actually it is running total but it reset to zero if Flag value is 0.
Here is the example of data along with required column
I created the following trigger to ensure that Mastercard or Visa are entered as the credit card type is a credit card table. The trigger works fine when I use one type, but when I add or, it doesn't work. Any advice? Thanks
/* THIS TRIGGERS VERIFIES THAT A VALID CREDIT CARD TYPE IS ENTERED*/
CREATE TRIGGER trg_accepted_credit_card on credit_card_payments for insert as begin
declare @card_type varchar(50)
select @card_type = (select card_type from inserted)
if exists(select 1 from inserted where @card_type != 'Mastercard' OR @card_type != 'Visa')
begin print 'Invalid Credit Card Type.' rollback transaction end end
I have an insert trigger which has to insert new records into 2 other databases. The first thing it has to do is get a maxid from a table in each of the databases before the insert. i was going to use a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statment before getting the ID and then do my insert. My question is two-fold, first is this the best way to do it. And since I have 2 separate selects to get the 2 maxids in the other 2 databases and tables, do I need 2 of these statements inside the trigger, before each insert, or a separate one for each select? Thanks
Hi All, I am using a trigger. I want to get the data of a row before updating inside this trigger and insert it into a backup table. Please anybody help me. Example with code is highly appreciated.
How the procedure will be called inside the trigger,whether first procedure followed by second or parallel it will execute?
CREATE TRIGGER [dbo].[InsertDatFXActualStaging] ON [dbo].[DatFXActualStaging]--change this table to DatFXActualStaging for INSERT AS BEGIN SET NOCOUNT ON;
I'm experiencing a problem I think I should not in my COM+ application. I will describe the setup first and then will expose the problem.
It's a simple COM+ application (dll). Inside it, there's a method to save an object A. Object A is persisted in a table in SQL Server 2000 that uses an identity field for the primary key. What this method does is the following: 1) Insert the record for Object A via ADO 2) Retrieve the Id for the object using SCOPE_IDENTITY via ADO and set it on the object 3) Execute an UPDATE statement based on a certain condition via ADO (this UPDATE statement will fire a trigger, however the trigger will not do anything since the record does not answer the criteria for the trigger) 4) Insert a record for another Object A via ADO 5) Retrieve the Id for the object using SCOPE_IDENTITY via ADO and set it on the object
When I get to step (5), an error is raised because SCOPE_IDENTITY returns NULL. This is as if it was returning the Identity value for the trigger that did not cause any INSERT on the UPDATE statement in (3). All the steps are performed using a single connection.
The trigger will duplicate the updated record in another table if a certain flag is set, so in my case, it was not set yet.
It's just weird that this would happen. If I delete the trigger, everything works fine. @@IDENTITY gives me the same problem. It's really as if the trigger was taking over or something and unless I put something between the two steps I get this error. There's one thing though. In step (3), I was using the adCmdText flag for the ADO statement. If I use adExecuteNoRecords it works fine. However I don't understand why it would be this way, I'm trying to understand why it's not working to begin with, even though the sequence of the steps performed should.
I'm updating one column using trigger but i am getting below error .
UPDATE failed because the following SET options have incorrect settings: 'NUMERIC_ROUNDABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value OR to do System.DateTime.Now in the application and then insert it in the table? I figure even small differences would be magnified if there is moderate traffic, so every little bit helps. Thanks.
Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.
Very new to SQL and trying to get this query to run. I need to sum the total trips and total values as separate columns by day to insert them into another table.....
My code is as follows;
Insert Into [dbo].[CombinedTripTotalsDaily] ( Year, Month, Week, DayNo, Day, Trip_Date,
I haven't a clue how to accomplish this.All the data is in one table. The data is stored by registration dateand includes county and number of students brokne out by grade.Any help appreciated!Rob
I have a table that writes daily sales each night but it adds the day's sales to the cumulative total for the month. I need to pull the difference of todays cumulative total less yesterdays. So when my total for today is 30,000 and yesterday's is 28,800, my sales for today would be 1,200. I want to write this to a new field but I just can't seen to get the net sales for the day. Here is some sample data. For daily sales for 6-24 I want to see 2,000, for 6-25 3,000, 6-26 3,500, and 6-27 3,500. I'm thinking a case when but can't seem to get it right.
I have created a Report using Visual studio-the report displays a subreport within it.
On the Subjective Report I have 12 values for each month of the year.
For the first month the value is =sum(Fields! Month_1.Value), and I have named this text box €™SubRepM1€™ The name of the subreport is €˜subreport1'.
On my Main Report, again I have 12 values for each month of the year. For the first month the value is =sum(Fields! Month_1.Value)*-1, and I have named this text box 'MainRepM1' The name of the main report is 'GMSHA Budget Adjustment Differentials'
The report displays both of the subreport and main report values but I now need to total these values together for each month in order to produce a grand total.
I have tried using the following to add the totals for Month 1 together, =subreport1.Report.SubRepM1 + MainRepM1 but this does not work and I get the following error message €˜The value expression for the text box 'textbox18'contains an error [BC30451] Name subreport1 is not declared'.
I feel that it should be a simple matter of adding the two sets of values together but I€™m having major problems trying to get these totals to work.
I'm trying to execute a stored procedure within the case clause of select statement. The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.
@val1 and @val2 are passed in
CREATE TABLE #TEMP( tempid INT IDENTITY (1,1) NOT NULL, myint INT NOT NULL, mybool BIT NOT NULL )
INSERT INTO #TEMP (myint, mybool) SELECT my_int_from_tbl, CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0 FROM dbo.tbl WHERE tbl.val2 = @val2
SELECT COUNT(*) FROM #TEMP WHERE mybool = 1
If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results. Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
Anyone has a "one sql statement" to get the total spaceused and totalspace allocated of an instance ? ie same as sum of relevance fieldsfrom sp_spaceused for each database in an instance, that works accrossversion of mssql from 6 onward.ThanksKD
I have a table named Prescription that consists of attributes like PatientId, MedicineCode, MedicineName, Prices of different drugs, quantity of different drugs(e.g 1,2,3,10), date .
I would like to get a summary of the total number and amount of different drugs in a specific period, the total amount of each type of drug.
I am trying to create a report on some data. I have about 8 tables and 30+ queries attached to those 15 reports. In one of those reports I want to get the percentage based on the data in the tables and queries. Say I have the minimum hours for an employee as 176 hours and the employee works for 227 hours in a month. I want to see the result in percentage.
My report looks something like this :
ID Name Oct Nov Dec Jan Feb March Total 001 alex 87.6% 104.1% 65.1% 50.2% 85.6% 002 Linda 87.4% 109.1% 68.1% 35.2% 90.8% 003 Jon 87.6% 104.1% 004 alex 87.6% 104.1% 65.1% 50.2% 85.6% 005 Linda 87.4% 109.1% 68.1% 35.2% 90.8%
For the 002 ID, though he has worked for Nov and Dec the total % is blank.
The formula that I used for all of these entries is :
=IIf(IsError([total]),"",[total])
and for the month it is : =IIf(IsError([Oct]),"",[oct]), nov and so on.
It works fine for all, but where ever there is blank in one field it doesn;t calculates for the others too..
I have a table in my db that has an identity column as the primary key. I have deleted all the data and have tried to use truncate table on it to reset the identity column. The table has a number of foreign key constraints so it will not truncate. Is there any way to reset the indentity column without removing the constraints?
FUNCTION [dbo].[fn_concat_boxes](@item varchar(10), @week int, @type char(1)) RETURNS VARCHAR(100) AS BEGIN
DECLARE @Output varchar(100)
SELECT @Output = COALESCE(@Output + '/', '') + CAST(quantity AS varchar(5)) FROM flexing_stock_transactions WHERE item = @item AND week = @week AND firm_or_commission = @type GROUP BY quantity ORDER BY quantity
RETURN @Output
END
gives the same output every time it is called, despite different parameters passed. @Output does not change as expected.
Hi, consider my table, s.no priority status 1 2 pending 2 3 pending 3 0 completed 4 1 pending 5 0 completed Now, if I insert a record as, 6 1 pending the priority must be reset as follows, s.no priority status 1 3 pending 2 4 pending 3 0 completed 4 2 pending 5 0 completed 6 1 pending Im using c# and sql server. How can i do it? Thanks, Jasmeeta.
I have a few SQL tables that use an auto incrementing integer key field, ie it has 'is identify' set to yes The tables have been used for testing while the application was developed. I plan to delete all data from these tables when the application goes live. Is there a way to start SQL counting from 1 again without deleting and re-creating thr tables?
Hi Forum, I have a .mdf that I have used to test SQL data app. I want to reuse the .mdf; delete all added data and reset Customer_ID primary colomn back to 1. All good info appreciated thanks Paul
I have a SQL data source triggered with this code: string searchstring = TextBox2.Text; SqlDataSource1.SelectCommand = "mod_search_all_sel"; SqlDataSource1.SelectParameters.Add("search_str", searchstring); SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure; The second time this fires however, the SelectParameters.Add function creates another paramter and the stored procedure blows up. How do I do a reset like in old Classic ASP where you would set the DS = Nothing?
In my application , DB has a large table. I write a small program to clear the table whenever the size of table is over 50 MB. At that time , I want to reset identity as 1. How can I do that? Currently , my program delete old table and generate a new one with the same schema when the table is too large.But this is kind of ugly.
