I have 3 tables...TableA, TableB, TableC
TableA - Personal Information
PersonalInfoId (Primary) , First Name,Last Name
TableB - Personal Information To Department ID
ReferenceID, FKPersonalInfoId, FKDepartmentId
TableC - Department
DepartmentId, DepartmentName
I am coding Asp.Net VB using VWD express with Sql Server Express. I know how to create a stored procedure to delete, insert and even update a record in TableA, TableB, TableC respectively.
If I need to delete a record in TableC, which has a related record in TableB, I have read that I need to use a Trigger. I never have used a Trigger and it is new to me. Can someone point me a way on how to use one in this case of my deleting scenario. Pretty much, if a user clicks on a delete button, and deletes a record in my TableC, I dont want a FKDpartmentId in my TableB that doesnt exist anymore because it was deleted in TableC or prevent a user from deleting that record till the relationship in TableB is no longer valid.
In the same vain, If I have a input form which ask the user to enter their First Name and Last Name and Department, i would like to add those records in TableA for First and Last Name, TableB for the Department. Once again, how do I create a Trigger that if I insert a record in Table A to also insert the information for Department in Table B, if its successful in my stored procedure. Hope that made sense.
Thanks.
Hi I'm new to SQL. The primary keys of the tables I use or not autonumbered. I need to insert data and delete data. When I need to insert data al the primary keys should change. example
1 tim 2 frank 3 jan 4 klaus
I need to insert a new name at place number 3. So jan get's number 4, Klaus number 5.
The same thing goes if I want to delete a row
How can this be done with a stored procedure??? anybody an idea Or are there script available for things like this? Tim
How could I possibly write a SP for Insert, Update and Delete for the below tables under this condition. On some conditions they need to be queried, initially for Insert, first the data should be inserted to PROFILES table and then to ROLES table. When inserting, if an entry is new then it should be added to both the tables. Sometimes when I make an entry which is already present in PROFILES table, then in this case the value should be added to the ROLES table. And a delete query to delete rows from both the table.
CREATE TABLE PROFILES( USER_ID varchar(20) UNIQUE NOT NULL, Name varchar(40) NULL, Address varchar(25) NULL
Hopefully someone can help me with this, or tell me if it's even possible for SQL Server 2005. I'm new to this and learning as I go along.
Here's what I want to do - I have a couple of queries running off a linked server. I want to take the results of those queries and insert them into a table in SQL Server, but first I want to delete the current contents of that table. I want to build a stored procedure that I can run monthly to temporarily store this data for review; once I'm ready to review the next month I want the procedure to delete the data in the table and then insert the next month's data. Hopefully that makes sense.
Is it possible to build? Anyone have any examples of such a thing, or can you point me someplace that might be able to help?
hi guys,I am using SQL server 2005, i created a table of 4 columns. I want to create a stored procedure which will insert delete and modify the table using flag. all the three insert, delete and update must work in a single stored procedure. pls help me out. Thank You.
Hi All, As known its recommended to use stored procedures when executing on database for perfermance issue. I am thinking to create 4 stored procedures on my database (spSelectQuery, spInsertQuery, spUpdateQuery, spDeleteQuery) that accept any query and execute it and return the result, rather than having a number of stored procedures for all tables? create PROCEDURE spSelectQuery ( @select_query nvarchar(500) ) as begin
exec sp_executesql @select_query, N'@col_val varchar(50) out', @col_val out
I have a problem with an existing stored procedure that is used to insert a new entry to a table (using an Insert statement).
I have been using the @@Identity global variable to return the identity column (id column) back to the calling routine. This has worked fine for years until recently an ‘after insert Trigger’ has been added to the table being updated.
Now the @@Identity is returning the identity value of the trigger that was called instead of the original table insert.
Does anyone know how I can code around this issue (without using a select statement within my stored proc, as these have been known to cause locks in the past).
I have one stored procedure for insert, update and delete operations, I need automatically logged the insert,update and delete operations. How can I set auto logged mechanism and how can I access the logs in c#? Thanks
I have to control my business rules in a Instead of Insert, Update Trigger.
Since the Control Flow is quite complicated I wanted to break it into stored procedures that get called from within the trigger.
I know that Insert Statements embedded in a Instead of Trigger do not execute the Insert of the trigger you are calling.
But... If I embed stored procedures that handle my inserts in the Instead of Insert trigger call the trigger and put in a endless loop or are the stored procedure inserts treated the same as trigger embedded inserts.
I HAVE TWO TABLES IN THE DATABSE AND THE SECOND TALE SI FOR AUDITING. I WANT CREATE THE TRIGGER ON FIRST TABLE SO THAT I CAN PUT THE STATUS LIKE INSERT,UPDATE OR DELETE IN THE STATUS COLUMN IN SECOND TABLE. CAN SOMEBODY HELP IN WRITING THAT TRIGGER..? HOW CAN I DETERMINE WAETHER THE RECORD IS BEEN INSERTED OR UPDATED OR DELETED.
DO I HAVE TO WRITE A SEPERATE TRIGGER FOR EACH ACTIVITY..OR I CAN WRITE IT IN THE SINGLE TRIGGER..?
I am having problems with a trigger that is designed to audit changes to a particular field in a table. If that field is updated, then the old record is inserted into an audit table.
This trigger never fails when I run test data against it from Query Analyzer. It works some of the time when the web application updates it, fails other times.
Typically, multiple records are updated at the same time. Any ideas?
Here is the Trigger:
create trigger t_u_product_rate_detail on product_rate_detail for insert, update, delete
--Set values so function isn't executed a bunch of times select @auditdate = getdate(), @audituser = suser_sname()
if exists (select * from inserted) begin if exists (select * from deleted) begin insert into product_rate_detail_audit_log select d.product_rate_detail_id, d.product_rate_id, d.day_of_week_id, d.ad_size_id, d.rate, d.plan_vol, d.plan_freq, @auditdate, @audituser, 'U' from deleted d join inserted i on i.product_rate_detail_id = d.product_Rate_detail_id where (d.rate <> 0 and d.rate is not null) and i.rate <> d.rate -- this determines if the rate has changed. end end
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
I have to create a trigger that will log who changed information on a table and when (NOT what they have changed).
My idea is to get the users name and see if it is in a table if not create it and get the associated ID, also get the ID of table that was accessed along with the ID of the type of task that was performed. Take this data and insert it into a table.
I have a question about trigger. I have a table called "Users", it has a ID (auto increment), UserName, FirstName...ect. The question is if one row is deleted, I want to put this row into "Users_Histroy" Table whose structure is the same as "Users" Table and then delete this row in "Users" Table.. How can I achieve this? I am pretty new about triggers. Thank you very much.
What statement do I use, as part of an insert trigger, to insert xml data from the xml database to a flat file database, to check if a record with a specific ID exists to delete first then insert the changed record, instead of adding the changes or an updated from the original xml database.
What I’m trying to do is take the xml formatted data out of one sql server database and insert the data only in that xml into a another sql database. So I can play with the data.
Problem is if the data in the xml is updated or changed for a specific record on the original xml database then the trigger inserts another copy into the created database (which I don’t want).
I have an c# windows service, which is running on the same machine where my mssql server is installed. This service must be informed for each insert/update/delete event on one specific table.
My idea was to create an CLR Trigger for this table which can communicate with this service via .NET remoting. But the problem is, that the system.runtime.remoting assembly is not integrated within the mssql clr enviroment => i can't use remoting.
I am using Sql Server 2000. I have a customer table with fields - CustId, Name, Address, City, StdCode, Phone. I used to insert entries in this table from an excel file. One excel file will contain thousands of customer. In this table combination of StdCode and Phone should not be repeated. If I do it in my VB.Net coding.then application gets drastically slow. So I want to write a procedure or trigger for this. Here what I will do, I will send all records into database then this trigger or procedure will check for any existing entry of combination of StdCode and phone. If entry exists then this will delete new entry or will not allow this new entry. Is this possible to do using Trigger or stored procedure?
help on CREATE stored procedure delete and after insert where not exist in one stored procedure in table_B
Code Snippet CREATE PROCEDURE [dbo].[delete_from_table_B] @empID varchar(500) as DELETE FROM table_B WHERE charindex(','+CONVERT(varchar,[empID])+',',','+@empID+',') > 0
---HELP from this ponit how to insert ? after where not exist
IF @@ROWCOUNT > 0
BEGIN
insert into table_B set (empID,ShiftDate,shiftType) where not exist
I need to create a stored procedure which would delete just the photo of a student once he graduates. I found out that a delete SQL statement will delete a whole row. Is there a way to just delete the Photo and leave the rest of the data untouched.
I created a insert stored procedure but It was not working correctly Could you correct the code? I am trying to insert contract information on contract table but before that I want to check the studentID in student table and contactId in contact table if they exist I want to insert into the contract table
Please help!
************************************************** *** My contrat DDL is follows
create table contract( contractNum int identity(1,1) primary key, contractDate smalldatetime not null, tuition money not null, studentId char(4) not null foreign key references student (studentId), contactId int not null foreign key references contact (contactId) );
************************************************** *** My insert stored procedure is follows
create proc sp_insert_new_contract ( @contractDate[smalldatetime], @tuition [money], @studentId[char], @contactId[int]) as if exists (select s.studentId, c.contactId from student s, contact c where @contactId = contactId and @studentId = studentId)
begin insert into contract ([contractDate], [tuition], [studentId], [contactId]) values (@contractDate, @tuition, @studentId, @contactId)
end else print 'studentId and contactId are not valid, please try another studnetId and contactId' go
I want to delete some rows in a table using a Stored procedure. I am using MS sql 2000 using Enterprise manager. I want to delete all rows that have col. status listed as shipped.
/Create Procedure [DeleteOld] AS / /Delete Equipment /FROM Yahoo /WHERE Status <> 'shipped'
I have two rows of data, one row has a col. names status, and in that row of data says "shipped". The other row says "hold" in the place of "shipped".
The procedure is written, and is setting under that database ready to be run. I do not get any error's when I excute the procedure from SQL query, but it just does nothing when run!
Hi, I'm having a hell of a time trying to get a delete query to work properly. I have an existing query that finds records in the database that have expired and now I want to delete those records. I tried wrapping a delete around it, but it deletes everything.
How can I delete the results of this procedure? Thanks!
SELECT rssName, [Days Old] FROM (SELECT rssName, DATEDIFF(D, timestamp, getdate()) AS [Days Old] FROM tblRSSFeeds) AS feeds WHERE [Days Old] > @expireTime
I have looked at the membership and roles stored procs from Microsoft and noticed that most of them are wrapped into a transaction. Ok some of the stored procs updated more than one table in which case it makes sense to wrap the code into a transaction. Our stored procs are a little simpler and insert, update or delete only one table for the most part. My question is: What is good practice, should I wrap my stored procs in transactions or because I am only updating one table leave it the way it is, see sample below: Please advise, newbie ALTER PROCEDURE [dbo].[syl_Category_Insert] @CategoryName nvarchar(64), @LanguageID int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRYINSERT INTO [syl_Categories] VALUES( @CategoryName, @LanguageID) SELECT SCOPE_IDENTITY() AS [CategoryID] RETURNEND TRY BEGIN CATCH --Execute LogError_Insert SP EXECUTE [dbo].[syl_LogError_Insert]; --Being in a Catch Block indicates failure. --Force RETURN to -1 for consistency (other return values are generated, such as -6).RETURN -1 END CATCH END
I have looked at the membership and roles stored procs from Microsoft and noticed that most of them are wrapped into a transaction. Ok some of the stored procs updated more than one table in which case it makes sense to wrap the code into a transaction. Our stored procs are a little simpler and insert, update or delete only one table for the most part. My question is: What is good practice, should I wrap my stored procs in transactions or because I am only updating one table leave it the way it is, see sample below: Please advise, newbie
ALTER PROCEDURE [dbo].[syl_Category_Insert] @CategoryName nvarchar(64), @LanguageID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
HiThis problem involves 2 columns in my Product table - ReleaseDate(varchar) and ReleaseClass(varchar)When a new product is Entered, the current date is inserted into ReleaseDate & ReleaseClass = NewWhen product is a month old, I want ReleaseClass to = RecentWhen product is a month 3 mths old, I want ReleaseClass to = NormalWhen the product is a month old, is there a method to automatically run a Stored procedure say to change the ReleaseClass field??any code or links on how i go about this would be appreciated + i've never used a triggerCheers!!
Hi, Please help me to find this answer. We know Trigger are a type of stored procedure,and can be activated whether by a insert ,update or delete event of a table. We also know that stored procedure are quick due to their execution plan which are already stored in the memory once complied. But what about triggers? what is the mechanism of triggers?How they work? And how fast they are from Tsql queries?Is there any mechanism to calculate or measure the efficiency of triggers? Please comment if anybody knows the answers. Thanks!! Joydeep
I have the following sp:@TABLE_NAME varchar(255), @CONTROL_ID int
AS DECLARE @sql varchar(4000) SELECT @sql = 'DELETE FROM [' + @TABLE_NAME + '] WHERE CONTROL_ID = 5'SELECT @sql = 'DELETE FROM [' + @TABLE_NAME + '] WHERE CONTROL_ID = ' + @CONTROL_ID + '' EXEC (@sql) When I use the the first SELECT line, it works great.When I use the second dynamic select, SQL raises an error: Syntax error converting the varchar value 'DELETE FROM [TABLE_SETTINGS] WHERE CONTROL_ID = ' to a column of data type int.What is wrong?
How to I return the row count as result of deleting some rows from a table? This this script correct below?? Here's my stored procedure script..
Code:
CREATE PROCEDURE dbo.sp_CleanUp_Table_tblSoldRaw /*************************************************************** ** CREATED: 12/04/2006 **************************************************************** ** DESCRIPTION: Clean the tblSoldRaw by deleting any data that ** is over 90 days old. We need keep the database ** as small as possible so the performance won't ** suffer on the server's and the client's machine. ** **************************************************************** ** NOTES: Hooked up to BookItOut Data Importer Windows Service **************************************************************** ** MODIFICATIONS: ** ** DATE WHO MODIFICATION DESCRIPTION ** ------------------------------------------------------------- ** ***************************************************************/ /*************************************************************** ** Uses: tblSoldRaw ***************************************************************/ AS DELETE FROM tblSoldRaw WHERE TIMESTAMP > (GETDATE() - 90)
