Triggers On Delete And On Insert && SQL Server 2000
Jul 20, 2005
Hi everybody,
I just wrote my first two triggers and from the minimal amount of testing I
have done, they work! However, I was hoping I could get some feedback from
those of you more experienced in writing triggers.
Here is the first one:
CREATE TRIGGER DecreInters ON Interaction
FOR DELETE
AS
declare @stu INT
declare @num INT
select @stu = Student_FK from deleted
select @num = (select Inters from Student where Student_Key = @stu)
UPDATE Student
SET Inters = @num - 1
FROM Student
WHERE Student.Student_Key = @stu
Here is the second one:
CREATE TRIGGER IncreInters
ON Interaction
AFTER INSERT
AS
declare @stu INT
declare @num INT
declare @last_rec INT
select @last_rec = @@IDENTITY
select @stu = (select Student_FK from Interaction where Interaction_ID =
@last_rec)
select @num = (select Inters from Student where Student_Key = @stu)
UPDATE Student
SET Inters = @num + 1
FROM Student
WHERE Student.Student_Key = @stu
Are there any shortcuts I could use or things I could do to make these
triggers more efficient. Please give me some feedback and let me know of
any problems that might possibly be caused due to my doing this improperly.
Thanks ahead,
Corey
View 1 Replies
ADVERTISEMENT
Mar 21, 2008
Hi,
I have 2 table that are exactly the same and want to create a trigger on table1 that will do the following:
Every time i delete from table1, the "Instead of delete Trigger" will automaticaly delete the data from table2 and not from table1.
Can anyone help me?
View 11 Replies
View Related
Jan 23, 2006
Hi All,
I'm trying to make a trigger on a table. If the data changed, the trigger will fire and copy the table into another table.
Can u give me examples on Triggers for Update..
Thanks
View 1 Replies
View Related
Oct 15, 2007
hi all,
i had a view in my project, i am inserting a record in to that view(View contains a identity key for a column), with in stored procedure i am inserting a record i am not passing the identity key value to the insert statement. The record is getting inserted,Based on the identity key(i am getting the identity key value with Scope_Identity()) and with that value i am inserting records into another two tables.In this scenario every thing is working fine.
but now i am trying to place a trigger(instead of insert trigger) on the view, when i placed,it is not inserting record into first table,so i am not able to get the identity value of that record and process failed.
how can this achived, let me know.
View 2 Replies
View Related
Jan 19, 2007
Hello Guys!i have been working with oracle with quite a time. No i migrated to sqlserver 2000 and i want to create a trigger on a table.the trigger function has to update the Modification field to getdate()whenever a row is being updated.i tried lots of thingsif anyone can help i would appreciate a lot!Regards
View 2 Replies
View Related
Oct 24, 2007
We have trigger on a table. The trigger implementation is to insert a record in another table. If any error occurred in the trigger then the trigger should log the error to a file on the file system.
CREATE TRIGGER [myTRIGGER] ON [dbo].[MyTest]
AFTER INSERT
AS
declare @errorcode int;
INSERT INTO MySecondTable (id, myvalue) values (null, 'hey')
set @errorcode=@@ERROR
if (@errorcode <>0)
print 'Error occurred with error code "' + CONVERT(varchar, @@ERROR) + '"'
Problem
If the insert statement fails, for example because of a null violation, then trigger aborts and never reaches the next step in the trigger T-SQL code.
IS this a limitation or there's somthing wrong on the above code?
Thanks
View 6 Replies
View Related
Sep 30, 2004
Hello all,
I am trying to change values of two tables in my sql server 2000 database. When one of the tables is modified in some way like adding/updating a record, I need a trigger procedure to copy this new data from the first table to the second. Problem is, how do I get the newly inserted or updated data from the first table? How do I specify that I only want the data which caused the trigger to execute? Anyone know?
Thanks,
Bob
View 1 Replies
View Related
Nov 13, 2006
Hi,
I have a parent table (Projects) and few child tables (Project invoices, Project Sub-consultants, etc). Typically, 'Projects' table contain details about projects. Other child tables contain specific information about a particular project, like invoices, details of sub-consultants, etc.
I introduced a 'After Delete' trigger in parent table, which should delete all rows in child tables, at once the row is deleted in parent table. The code I have used is as follows:
Declare @PID char(8) -- Project ID SET @PID = (Select ProjNo from Deleted)BEGIN delete from ProjInvoice where ProjectID = @PID END
Now, when I delete a row in parent table and see the child tables, only the particular field says 'NULL' and all other field remains. (I have tested the SQL statement as stand-alone query and it works fine).
What's wrong with the trigger?
Thanks in advance.
Sathya
View 6 Replies
View Related
Jul 23, 2005
Hi all,I am fairly new to using triggers and was seeking some help from thosethat have experience with them. I am looking to transfer data from aSQL 2000 database to a Visual FoxPro database on another computer. Iwould like to transfer about three fields of data to a VFP table eachtime an insert is made on the SQL table. I am some what familiar withthe structure of creating the trigger but here is what I would likehelp with: Selecting the SQL data to transfer, Connecting to VFPdatabase, Insert SQL data into VFP table.CREATE TRIGGER [xyz] ON [dbo].[AAA]FOR INSERT??? Select a,b,c from SQL table??? Connect to VFP Database and Table??? Insert into VFP table Values a,b,cAny information, tips, or even an example Trigger procedure would helpand be greatly appreciated.Thank you,Brett
View 1 Replies
View Related
Oct 23, 2014
I'm working on inserting data into a table in a database. The table has two separate triggers, one for insert and one for update (I don't like it this way, but that's how it's been for years). When there is a normal insert, done via a program, it looks like the triggers work fine. When I run an insert manually via a script, the first insert trigger will run, but the update trigger will fail. I narrowed down the issue to a root cause.
This root issue is due to both triggers using the same temporary table name. When the second trigger runs, there's an error stating that a few columns don't exist. I went to my test server and test db and changed the update trigger so that the temporary table is different than the insert trigger temporary table, the triggers work fine. The weird thing is that if the temporary table already exists, when the second trigger tries to create the temporary table, I would expect it to fail and say that it already exists.I'm probably just going to update the trigger tonight and change the temporary table name.
View 1 Replies
View Related
Jul 20, 2005
i'm in a bit of a bind at work. if anyone could help, i'd greatlyappreciate it.i have a web app connecting to a sql server using sql serverauthentication. let's say, for example, my login/password isdbUser/dbUser. the web app however, is using windows authentication.so if I am logged into the network as 'DOMAINEric', when I access myweb app, my web app knows that I am 'DOMAINEric'. but to the sqlserver db, I am user 'dbUser'.now, i for each table i have, i need to implement an audit table torecord all updates, inserts, deletes that occur against it. i wasgoing to do so with triggers. this is all fine for selects, inserts,and updates. for each table, i have an updatedby and an updatedate.for example, let's say i have a table:create table blah(id int,col1 varchar(10),updatedby varchar(30),updatedate datetime)and corresponding audit table:create audit_blah(id int,blah_id int,blah_col1 varchar(10),blah_updatedby varchar(1),blah_updatedate datetime)for update and insert triggers, i can know what to insert into theupdatedby column of audit_blah because it's in a corresponding row inblah. my web app knows what user is accessing the application, andcan insert that name into blah. blah's trigger will then insert thatname into audit_blah.however, in the case of a delete, i'm not passing in an 'updatedby',because i'm deleting. in this situation, how can the trigger knowwhat user is deleting? the db only knows that sql user 'dbUser' isdeleting, but doesn't know that 'dbUser' is deleting on behalf of'DOMAINEric'. is there any way for my app to inform the trigger toaccess my windows identity without having a corresponding row in thetable from which to pull that info?obviously, i could have each of my app's users log into SQL serverthrough Windows authentication; then i could just use SYSTEM_USER.but let's say, for performance's sake, it'd be better for me to useone sql server login. (i believe one user works better for connectionpooling purposes.) is there a way to get around this?(i'm hoping a built-in function exists that solves all my problems.)suggestions? resources?any help would be great appreciated.happy turkeys.Eric
View 2 Replies
View Related
Dec 16, 2005
Quote: Originally Posted by mrtwice99 Yes, but how would you figure out which row was "before" it?
Code:
SELECT id, sortOrder, name FROM daTable
WHERE id = 937 OR sortorder =
( select max(sortorder) from daTable
where sortorder < ( select sortorder from daTable where id = 937) )
View 5 Replies
View Related
Jul 1, 2007
Hi, I'm using SQL Server 2000 Personal Edition. I have created a table 'Student' which has 2 fields.
ID -- Varchar(10), Primary Key - contains ID of a student
NAME -- Varchar(50) -- contains names of student
ID NAME
------------------ ------------------------------------------
0107200701 abcd
0107200702 cdgdh
0107200703 iyiylklk
.
.
I want to delete a complete row (all entries) from the "student" table using/specifing 'ID'. What will be the SQL query for that?
View 1 Replies
View Related
Mar 24, 2006
Hi All! Im new here and Im not pretty sure if this thread really belongs here. I just want to ask why I kept on having errors in deleting a record in Enterprise Manager.
Everytime I delete a record / row in a table it prompts me of Invalid Object Name. I guess its because of my table name "my.table" because it has a "." (dot). Does MS SQL not enclosed my table name by brackets "[]" to ensure success of action?
Anyway, It was just a dummy table that's why my table name has a "." and Im pretty sure that this naming convention is a NO - NO but nonetheless, I think MSSQL should still be able to process my delete query regardless of the name of my table (Since in the first place it didnt warn me upon creating the table that the name is invalid)
Hope you can enlighten me.
thanks!
View 1 Replies
View Related
Apr 6, 2015
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).
This is on SQL Server 2008R2.
View 2 Replies
View Related
Mar 15, 2013
I am experimenting with using CDC to track user changes in our application database. So far I've done the following:
-- ENABLE CDC ON DV_WRP_TEST
USE dv_wrp_test
GO
EXEC sys.sp_cdc_enable_db
GO
-- ENABLE CDC TRACKING ON THE AVA TABLE IN DV_WRP_TEST
USE dv_wrp_test
[Code] ....
The results shown above are what I expect to see. My problem occurs when I use our application to update the same column in the same table. The vb.net application passes a Table Valued Parameter to a stored procedure which updates the table. Below is the creation script for the stored proc:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if exists (select * from sysobjects where id = object_id('dbo.spdv_AVAUpdate') and sysstat & 0xf = 4)
drop procedure dbo.spdv_AVAUpdate
[Code] ....
When I look at the results of CDC, instead of operations 3 and 4, I see 1 (DELETE) and 2 (INSERT) for the change that was initiated from the stored procedure:
-- GET CDC RESULTS FOR CHANGES TO AVA TABLE
USE dv_wrp_test
GO
SELECT *
FROM cdc.dbo_AVA_CT
GO
--RESULTS SHOW OPERATION 1 (DELETE) AND 2 (INSERT) INSTEAD OF 3 AND 4
--__$start_lsn__$end_lsn__$seqval__$operation__$update_maskAvaKeyAvaDescAvaArrKeyAvaSAPAppellationID
--0x0031E84F000000740008NULL0x0031E84F00000074000230x02119Test26NULL
--0x0031E84F000000740008NULL0x0031E84F00000074000240x02119Test36NULL
--0x0031E84F00000098000ANULL0x0031E84F00000098000310x0F119Test36NULL
--0x0031E84F00000098000ANULL0x0031E84F00000098000420x0F119Test46NULL
Why this might be happening, and if so, what can be done to correct it? Also, is there any way to get the user id associated with the CDC?
View 7 Replies
View Related
Feb 1, 2007
Hi,
I have a database created in server 2000, and now I have moved it to server 2005.
All works do fine, but there is a user which cannot be removed.
In the user properties window, the assigned schema is empty. The user is a db_owner of the database. When I was trying to update the user, it asked me for the login. The login is empty, but the field is disabled.
So my question is, how to remove this user?
Thank you.
Jensan
View 1 Replies
View Related
Nov 26, 2007
this is my Delete Query NO 1
alter table ZT_Master disable trigger All
Delete ZT_Master WHERE TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
alter table ZT_Master enable trigger All
I have troble in Delete Query No 2
here is a select statemnt , I need to delete them
select d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
I tried modified it as below
delete d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
but this doesn't works..
can you please help?
and can I combine these 2 SQL Query into one Sql Query? thank you
View 1 Replies
View Related
Jan 22, 2007
Hello, what i want is simple.
This is a simple forum, it has several topics (that the users can create), when a user create a topic, its stored in forum_topics. The user can then view the topic and post a response that is store in forum_answer, the user can also add this to his favorite list, forum_favorites is simple, contains a TopicID that refers to the topic, a username of the user that has the topic on his favorite list and a auto increment id to be able to delete specified topic favorites.
Now my question is: when a user posts a answer to Topic X, i want a predefined message to be sent to post_inbox for all the users that has Topic X in their favorite list.
How can i get MS SQL 2005 to get all the users from Topic X and then loop thru them and insert a new post into post_inbox?
Patrick
View 2 Replies
View Related
Jul 23, 2005
I've been reading the docs and playing around, but I'm still notgetting the difference. For instance,create table a(i int check(i>0))create table a_src(i int)gocreate unique index ai on a(i) with IGNORE_DUP_KEYgoinsert into a_src values(1)insert into a_src values(1)insert into a_src values(2)--insert into a_src values(-1)gocreate trigger a4ins on afor insertasselect * from insertedgocreate trigger afterins on aafter insertasselect * from insertedgoinsert into a select * from a_srcgodrop table adrop table a_srcI'm gettingi-----------12(2 row(s) affected)Server: Msg 3604, Level 16, State 1, Procedure a4ins, Line 4Duplicate key was ignored.i-----------12(2 row(s) affected)even the inserted quasi tables are identical.If I uncomment insert into a_src values(-1), I'm gettingServer: Msg 547, Level 16, State 1, Line 1INSERT statement conflicted with COLUMN CHECK constraint'CK__a__i__58FC18A6'. The conflict occurred in database 'ABC_1COMPEE',table 'a', column 'i'.The statement has been terminated.without any output from either trigger.So,in which situations will FOR INSERT be useful while AFTER INSERT won'tdo?in which situations will AFTER INSERT be useful while FOR INSERT won'tdo?
View 2 Replies
View Related
Jul 21, 2004
Hi
if i try to delete data via MFC from Microsofts SQL Server 2000
i get following message: Data can be read only.. Weird i can write and read already. do i have to setup anything in Micrsofts SQL Server 2000 ??
please help its urgent.
Greets
Indian
View 1 Replies
View Related
May 8, 2015
i would like to know it's possible to find all transaction(insert, delete,update) on a database for a day. if yes what can i do.
View 2 Replies
View Related
Sep 23, 2015
i use a single stored procedure to update many tables in sql server 2014 database, using defalut transaction isolation level we got random performance issues.
maybe it would better to use read uncommitted isolation level?
what's happens ,in the both cases( read committed, uncommitted) if the sp is called at the same time passing the same @key parameter?
this is a sample to show of the real stored procedure works:
CREATE PROCEDURE SP_Test
@Key int,
@Values1 Values1 readonly,
@Values2 Values2 readonly,
@Values3 Values3 readonly
[code]....
View 8 Replies
View Related
Feb 16, 2007
I have a SQLDataSource that gets it's data from a SQL 2000 database table. I have configured it to generate the Update/Delete commands, which look correct. I then have a GridView that is using this SqlDataSource to show the data with "Edit" & "Delete" buttons (the default ones from the GridView).
My problem is that while all commands (Edit, Delete) work on my local server, they do NOT work on my live server. In my connection string, I specifiy a username and password like this:
<add name="Project_Management.My.MySettings.WebReportsConnectionString"
connectionString="Data Source=Karlweb;Initial Catalog=WebReport;Persist Security Info=True;User ID=VbUser;Password=VbUser"
providerName="System.Data.SqlClient" />
I have access to change the permissions on my production server, so I gave this "VbUser" every allow permission I could find and still I could not Edit or Delete records. What I mean by they "do not work" is that when I click the Edit button, the GridView switches to edit mode, but when I click Update, the changes are not written. When I click the Delete button, the page just refreshes and the record is not deleted.
As this is working on my local server, I think the problem lies in some permission or configuration of the server. Does anyone have any suggestions of where I could look or what to change?
Thank you!
View 3 Replies
View Related
Dec 2, 2007
Using ASP.net 2.0 or Visual Web Developer Edition 2005 I could not find a single example with Datagrid Control or Form View Control, to achieve the basic functionality ofAdd/Edit/Delete Records with Remote SQL Server 2000 Database. When I searched for these examples I found many videos and examples using local database and in learning path of Visual web express editions, very goodexamples and videos using local SQL Server 2005 database, BUT not with the remote database.
My question Is it possible to get the basic functionality of Add/Edit/Delete Records with Remote SQL Server 2000 Database using ASP.Net 2.0 Datagrid and FormView controls?
This question looks like, a lazy developer question!! but, in my learning path I found GREAT videos in Visual Studio Web Developer Express edition and learned simple way of drag and drop the controls assign create local database and their connections, and few clicks to add/edit/delete records using datagrid and formview controls. In real life those are not much useful because many of web interfaces are with sql server 2000 and we need to convert them into ASP.Net. Similarly I wanted to drag and drop couple of controls and less coding etc. and accomplish basic functionality of add/edit/delete records using remote database sql server 2000.
If possible please help me out.
Wondering is it possible to get the functionality of add/edit/delete records using datagrid/formview with remote sql server 2000, the way they explained in bigginers learning videos ... I am referring to (http://msdn2.microsoft.com/en-us/express/aa700802.aspx)
If possible and you think it is possible please guide me to that URL where I can learn the great functionalty n implement..!!
View 1 Replies
View Related
Aug 20, 2007
Hi
I want to delete the duplicate rows from two tables and get the resultant non-duplicate rows from both the tables into another table
View 4 Replies
View Related
Jan 22, 2007
Hi there
I' d like to make a trigger which will come with some kind of message ( maybe jscript?) or printout, each time
I insert new client into the Client table. Clent table has ClientID, ClientName, Address, EMail, and ContactPerson.
Please help
M.D.
View 1 Replies
View Related
Aug 6, 2004
I have written an Insert Trigger to examine newly inserted records and set some values. However, each time a record is inserted, all records are checked. How can I make the trigger work only on newly inserted records?
View 5 Replies
View Related
May 18, 2001
Do INSERT triggers not execute on BULK INSERT statements? Do they execute only once, after all the insertions are completed?
Is there some way to get an INSERT trigger to execute on every single row of a BULK INSERT?
Am I a dope? :)
Thanks in advance.
View 3 Replies
View Related
Nov 20, 2000
I have a situation where I need to create an insert trigger on table a which will create a corresponding record in table b. However before I insert the record i must obtain the max value for the record in table b and increment it by one. I have all this working. My question is if I just put a begin and commit with this statement is there a chance that when 2 users insert at the same time the max value may be incorrect say for instance
CREATE TRIGGER tr_cms_prov_ins ON provider
FOR INSERT
as
declare @ndentPrid char(3),
@nxtgenPrid char(7),
@fname varchar(40),
@lname varchar(40)
begin tran
select @ndentPrid = max(provider_id) from providerdnt
if @ndentPrid is null
set @ndentPrid = 1
else set @ndentPrid = @ndentPrid + 1
insert into dental..provider (provider_id, first_name, last_name, collections_go_to)
select @ndentPrid, first_name, last_name','YYYYYYYYYYYYYYYNNNNN' from inserted
commit tran
Will this do it or do I need to enforce some type of locking to handle the max value. There are no inserts into
table b directly only by the trigger insert on table a
View 1 Replies
View Related
Oct 26, 2006
Hello all,
I'm in the process of debugging a trigger i've written (which hasn't been any fun) and i have a question.
records for the table with the trigger are only inserted from a SP using an INSERT INTO ... SELECT statement. Multiple rows at a time.
question is..
does the trigger fire once for that insert into ... select or does it fire for each new row inserted by that statement. I was under the impression that it fires for each row but i'm not sure now because the table the trigger inserts to only has one row after the SP is run.
any help is greatly appreciated.
Will
View 2 Replies
View Related
Apr 3, 2008
I created a trigger which looks like
Create TRIGGER UpdateGenTables3 ON AWSWQ_Quizzes
After insert as
Begin
SET NOCOUNT ON
declare @TableName varchar(20),@Title varchar(20),@code varchar(20),@Gcode varchar(20),@uppercode varchar(20), @Description varchar(50)
DECLARE DemoCursor CURSOR FOR
Select QuizID,QuizTitle from AWSWQ_Quizzes group by QuizID,QuizTitle
OPEN DemoCursor
FETCH Next From DemoCursor INTO @code,@Title
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO gen_tables (TABLE_NAME, CODE, SUBSTITUTE,UPPER_CODE,DESCRIPTION,OBSOLETE_DESCRIPTION)
VALUES ('PRE_REQ_TESTS', @code, '', @code, @title, '')
FETCH Next From DemoCursor INTO @code,@Title
END
CLOSE DemoCursor
DEALLOCATE DemoCursor
end
The problem with my trigger is .I am not able to insert into my table AWSWQ_Quizzes the error i get when i try to insert is
Violation of PRIMARY KEY constraint 'pkGen_TablesTABLE_NAME'. Cannot insert duplicate key in object 'Gen_Tables'. The statement has been terminated. ---> System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'pkGen_TablesTABLE_NAME'. Cannot insert duplicate key in object 'Gen_Tables'.
View 2 Replies
View Related
Nov 9, 2005
I have created a simple data import package using the SSIS Import and Export Wizard in Visual Studio 2005. All done in a bout 2 min. great stuff.
View 4 Replies
View Related