I wanted to know if it is possible to run a stored proc (report) when a new record is inserted / updated into a certain table. Also if it could do so for records with a column that meet a certain criteria ie. sell description like '%test%'
View 2 RepliesI am looking to created a trigger that inserts records into a log table to show the stored porcedure that has updated a specific column to a specific value in a specific table
At present I have:
CREATE TRIGGER [dbo].[trUpdaterTable]
ON [dbo].[t_account]-- A DB level trigger
FOR UPDATE
--Event we want to capture
AS
IF update (document_status)
[Code] ...
However this doesn't appear to bring back the procedure that triggered the update...
The value the trigger should update on is document_status = 0
DDLProcExecutedByEventDate
NULLNULLLOMBDAadministrator2015-06-25 07:42:01.677
NULLNULLLOMBDA im64602015-06-25 07:51:34.503
NULLNULLLOMBDAadministrator2015-06-25 07:52:01.610
NULLNULLLOMBDAadministrator2015-06-25 08:02:01.417
CREATE TRIGGER [dbo].[trTableupdateaccount] ON [DoesMore].[dbo].[t_account]
[Code] ....
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)
[Code] ....
how to track how many rows updated or deleted per day in a single table and load the information in another table .
View 7 Replies View RelatedI have several reports that are looking for a code within a certain set of codes or ranges. The specific list of codes to be including is determined by the end user. Currently my "IN" statement can be a hundred lines, listing several ranges, lists of specific codes, etc. I am constantly getting asked what codes does it include, is this code included, etc. Sometimes they'll give me a printed 10 page list of codes and want me to compare to what I have included in the report. Not ideal in the slightest.
What I'd like to do is have a table or a file of some kind somewhere where the end user can view the codes contained, add new ones, and delete ones they no longer want. Then I'd like to be able to just reference that file in my IN statement. Leaving the responsibility of listing the correct codes on them.
I have a report that summarizes hospital readmissions. Some months may only have a female or male patient that is readmitted but, I want to show both months either way.
create table dbo.Scott_TEST
(
YearMonth char(9),
Gender char(1),
NumOfFemale int,
NumOfMale int
[Code] ....
hi all,
I want to execute a store procedure but my query returns more than one value. how can I asign value for that. Here is the code. Please help me out.
problem is @aaa where i cannot set the value.
Thanks
Regards
Sudadg
CREATE PROCEDURE movetable
@id int
AS
declare @VType varchar(10)
declare @count int
declare @aaa varchar(50)
set nocount on
create table #temp1(id int identity(1,1),Ac_Code varchar(50))
select count(*) from Journal_Table where Voucher_No=@id
set @count=1
set @count=@@rowcount
while @count <>0
begin
set @aaa=(select Ac_Code from Journal_Table where Voucher_No =@id)
insert into #temp1(Ac_Code) values(@aaa)
set @count=@count-1
end
select * from #temp1
GO
I have two different SQL 2008 servers, I don't have permission to create a linked server in any of them. i created a trigger on server1.table1 to insert the same record to the remote server server2.table1 using OPENROWSET
i created a stored procedure to insert this record, and i have no issue when i execute the stored procedure. it insert the recored into the remote server.
The problem is when i call the stored procedure from trigger, i get an error message.
Stored Procedure:
USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
When i try to insert a new description value in the table i got the following error message:
No row was updated
the data in row 1 was not committed
Error source .Net SqlClient Data provider.
Error Message: the operation could not be performed because OLE DB
provider "SQLNCLI10" for linked server "(null)" returned message "The partner transaction manager has disabled its support for remote/network transaction.".
correct the errors entry or press ESC to cancel the change(s).
We are getting data feed from Oracle database in our project. Everyday we will need to track if any rows got inserted/updated/deleted in the source and get that update right into our data warehouse.
Currently we are taking a dump of the required table (as it is) to our staging DB and comparing it with previous day data to track the changes (column by column comparison). This approach is working currently but has performance bottleneck. There is no tracking column (eg. last modified date or time) in source that will give us any idea of what got changed. Also there is no identity key or primary key in the source data.
Is there a way in SQL Server to get that inserted/updated records only instead of comparing column by column to track the changes?
I have a table with constantly changing data - stock.
I want to start monitoring the value of stock at the end of each month. I can do this with a simple query, export the results to Excel and store on the network.
My question is:
Are there better ways to do this with SQL Server?
I thought of a monthly "job" that does the query and outputs to a file. (Need to be able to look at each month separately though for trend monitoring.)
Then wondered, if I should have an extra table to store the data and write queries on that in the future?
I have not yet succeeded in getting an aswer to this in a previous post, so I'll try again and rephrase the question
I have 2 fields, 1 called 'Created' the other 'Updated'
I would like to create a function, user procedure ,whatever in SQL something along the lines of...
FUNCTION myUID()
RETURN Date(Today)+Time(Now)+USERNAME
END FUNCTION
so that the value returned by the above pseudo code is something like '20080526T21:01:05.620SamL' where the date part is in yyyy0m0d format
I have got this working fine for the default 'Created' field but the calculated (and persisted) field comes up with something about being non-deterministic and refuses to play
Clearly something along these lines is a very straightforward requirement (I have it working fine in my 20+ year old database) , but have spent a week trying to get an answer (even from this forum) without success, so would be grateful for any help that you can provide me with to get round this barrier
Hi guys, may I know is it possible to create an update trigger like this ? Assuming there are two database, database A and database B and both are having same tables called 'Payments' table. I would like to update the Payments records on database A automatically after Payments records on database B had been updated. I can't use replication because both tables might having different records and some records are the same. Hope can get any assistance here, thank you.
Best Regards,
Hans
Version 2008 R2
The stored procedure has the dependency on the table that was altered.
we have a business use case that requires a software dashboard to display the current production backlog in our small business. Using SQL Server 2012 Express.ABC Co is our largest customer, they order a large number of items in a monthly order. We send these items in several shipments during the month, so the actual amount outstanding on each ABC Co order slowly decreases through the month. These orders are stored in the following tables:
ABC_OrdersSummary - stores each order - order no/date/total amount
ABC_OrderedProducts - stores each product - order no/product code/quantity
ABC_ShippedProducts - stores each shipped product - order no/product code/quantity
Products - stores all our products - product code/price etc.
To calculate the current outstanding orders value for ABC Co, we need to get the total of unfinished orders, then cycle through what has already been supplied on these orders, and subtract this value from the total unfinished orders value.Is there a way to keep a running total in the ABC_ Orders Summary table of the amount outstanding on each order? It wouldn't matter if it only updated every 5min - it'd just be more efficient than 10-15 software instances all calculating it at once.
Hi -
apologies if this is not the right forum for this - I've searched a bit and this seems to be the best fit.
I have the following problem: I want to update records in a table that fit certain criteria. The way the insert logic works make sure that there will always be only one record that fits the criteria and I'd like to get the ID value of that record once the update went through. So here is what I tried:
1 UPDATE Timeslot
2 SET StartTime = @StartTime, EndTime = @EndTime
3 WHERE (ProfessionalID = @ProfessionalID) AND (ProviderLocationID = @ProviderLocationID) AND (RequestID IS NULL) AND (StartTime > @StartTime) AND
4 (EndTime < @EndTime);
5 SELECT SCOPE_IDENTITY()
My hope was that the select scope_identity would return the Timeslot ID of the row that was affected, but it doesn't. How do I get that row?
Thanks!!!
Oliver
I have some set of records in my table.
The same set of records will be updated often. Now I have a column as "lastupdated"
While i am displaying the records in a datagrid, The LAST UPDATED record should only be displayed.
Means, the recently updated records should be displayed in datagrid.
Pls give me the sql code / i am also in need of a Stored procedure for this.
I am working in SQL 2005
I want to build an windows application by using a visual C# to Notify the user that his data in the database had been changed ..such like "New Message In Your Mail Box Alert"..So I need to know if there is way that to let the SQL Server send a notify (just like Trigger) ..
View 1 Replies View RelatedDoes anyone know how to get the indentity of the last updated record in a table. I need it for a cascading trigger.
Thank you,
Jody
I am trying to write a trigger that, on an update, will check to see if more than one record is being updated. If more than one record is being updated, rollback the transaction and print a message. If not, then commit the transaction and print an OK message.
My problem is I am having troubles writing this trigger. I will post my SQL trigger code so far.
I am new with Triggers, so any help or advice is appreciated.
Code:
CREATE TRIGGER trgPaperCheck
ON BOOK
AFTER UPDATE
AS
IF UPDATE(Paperback)
BEGIN
DECLARE @count AS INT
SELECT @count = COUNT(Paperback)
FROM Book
IF @count > 1
BEGIN
PRINT('Only one Book record can be updated at a time!')
ROLLBACK TRANSACTION
END
ELSE IF @count =1
BEGIN
PRINT('Paperback Updated')
COMMIT TRANSACTION
END
END
Hi,
I was opening a web page, that has a grid view representing the records of a table, I updated a record of the sql table by mistake, but I am not sure which record,
is there any way in Sql server to get the last record just updated few minutes ago. I hyave admin permissions on the DB.
Also, is there a way to know the value that was just there before the changes?
Thanks a lot.
who i can to list the content of all store procedures and funtions in text file ?
View 3 Replies View RelatedHi
I am new to SQL programming. This is what I am trying to do in store procedure.
Alter a table - adding two columns.
Then update those columns
Both the above action in same procedure .
When I execute them - it complains that the columns I am adding in the first part of store procedure , does not exists.
So the SP looks like this
create store procedure <name> as
alter table <name> ADD
column_1 nvarchar (256),
column_2 nvarchar (256);
update table
set
column_1 = <condition>
column_2 = <condition>
The SQL complains -
invalid column name column_1
invalid column name column_2
Can some one help please...
hi, good day,
when i run query in query analyzer
exec sp_test '2006-07-21'
it show result , however when i run it using BCP tools
exec master..xp_cmdshell 'bcp "exec sp_test '2006-07-21' " queryout c: est.txt -c '
it give me error which is something like "invalid object #temp_tbl",
i do using temporary table in store procedure
how to we solve it ? seem that bcp not support temporary table ? really need guidance from expert here ,
thank you :eek:
Hi
I have an application which get any change from database using sql dependency. When a record is inserted or updated it will fire an event and my application get that event and perform required operation.
On the event handler I am usin select ID,Name from my [table];
this will return all record from database.
I just want to get the record which is inserted or updated.
Can u help me in that.
Take care
Bye
I have a table that contains a field LastUpdated. This field should contains the date of the last update of the record.
Is there a way to auto-update this field value to the getdate() value each time the record is "touched" by an UPDATE sql command?
Thanks in advance,
Joannes
I would like to show when leads updated last their records in database. An automated report that tells me when the last date was that the leads updated an entry, only 1 entry per lead.
select Lead,LastUpdated from dbo.KPITbl
I have a table with data that looks like this:
Lead LastUpdated
----------- -----------------------
JOHN SMITH 2008-03-26 08:45:00
JOHN SMITH 2008-03-20 09:33:00
MEG RYAN 2008-02-21 16:16:00
JOHN SMITH 2008-02-21 16:19:00
MEG RYAN 2008-02-21 16:22:00
JOHN SMITH 2008-03-28 16:10:00
JOHN SMITH 2008-03-28 08:49:00
JOHN SMITH 2008-03-23 19:23:00
MARK MCRAE 2008-03-27 03:12:00
MARK MCRAE 2008-03-26 08:48:00
MARK MCRAE 2008-03-26 08:46:00
JOHN SMITH 2008-03-26 08:47:00
JOHN SMITH 2008-03-26 08:48:00
ALLAN WHITE 2008-03-26 08:43:00
ALLAN WHITE 2008-03-26 08:40:00
JOHN SMITH 2008-03-26 08:48:00
Thank you appreciate it.
Hi,
I was opening a web page, that has a grid view representing the records of a table, I updated a record of the sql table by mistake, but I am not sure which record,
is there any way in Sql server to get the last record just updated few minutes ago. I hyave admin permissions on the DB.
Also, is there a way to know the value that was just there before the changes?
Thanks a lot.
I have a sp that will do two select from two table. now, can datareader read both table or only dataset can? if datareader can? how to handle it?
View 1 Replies View RelatedHI
I have a problem related Store Procedure, that i am trying to extact a value from Database (Like FirstName,LastName,Email Address) through Store Procedure and Display it in the DropDownList(Like: FirstName LastName ,(xyz@xyz.com)) , and this is working correctly.
Now i try to check the value at the same time if it is NULL value in the Database then pass EmptyString to the DropDownList Like ("" "" ,(xyz@xyz.com))
how i can do that in the store procedure.
Comments will be appreciated.
Does anyone know how to save the output of a stored procedure into a table? I would like to be able to use some procedures such as the sp_helpfile and dbcc sqlperf(logspace) and save to output into a table. Does anyone have any ideas?
View 2 Replies View RelatedHi people.
I need some help to export a table to Microsoft Access using a Store Procedure.
My Store Procedure will pupolate a temporary table with some information. After that I want that the store procedure export this table to Access.
Is there any script or command that can do it? And how can i use it into a Store Procedure?
Please, if possible give a example.
thanks in advance
:confused:
While writing store procedure in db most of Time i will Use common Table to write select queries for selecting more than seven table whether it reduce speed performance or it won't
;with cte
{
}
I have following hash table in C#.
Rank Slug
1 ABC
2 XYZ
3 PQR
I want to pass hash table or data table to the store procedure and use the looping inside the store procedure.