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?
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
Hi everybody,I just wrote my first two triggers and from the minimal amount of testing Ihave done, they work! However, I was hoping I could get some feedback fromthose of you more experienced in writing triggers.Here is the first one:CREATE TRIGGER DecreInters ON InteractionFOR DELETEASdeclare @stu INTdeclare @num INTselect @stu = Student_FK from deletedselect @num = (select Inters from Student where Student_Key = @stu)UPDATE StudentSET Inters = @num - 1FROM StudentWHERE Student.Student_Key = @stuHere is the second one:CREATE TRIGGER IncreIntersON InteractionAFTER INSERTASdeclare @stu INTdeclare @num INTdeclare @last_rec INTselect @last_rec = @@IDENTITYselect @stu = (select Student_FK from Interaction where Interaction_ID =@last_rec)select @num = (select Inters from Student where Student_Key = @stu)UPDATE StudentSET Inters = @num + 1FROM StudentWHERE Student.Student_Key = @stuAre there any shortcuts I could use or things I could do to make thesetriggers more efficient. Please give me some feedback and let me know ofany problems that might possibly be caused due to my doing this improperly.Thanks ahead,Corey
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) )
This isn€™t an problem as such, it€™s more of a debate.
If a table needs a number of update triggers which do differing tasks, should these triggers be separated out or encapsulated into one all encompassing trigger. Speaking in terms of performance, it doesn€™t make much of an improvement doing either depending upon the tasks performed. I was wondering in terms of maintenance and best practice etc. My view is that if the triggers do totally differing tasks they should be a trigger each on their own.
I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.
It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog
how I can get this corrected so I can get the Maintenance Plans to run correctly.
I have tried deleting and recreating the Plan but to no avail
I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.
Background: Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software...
Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common denominator....ie, a 'recipe' of steps that will work on all DBMS's.
The Problem: There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name.
This appears possible in SqlServer too --...as long as there are no CASCADE operations. Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table.
So -- please correct me if I am wrong here -- it appears that the operations would be along the lines of: a) Remove the Foreign Key references b) Copy the table structure, and make a new temp table, adding the column c) Copy the data over d) Add the FK relations, that used to be in the first table, to the new table e) Delete the original f) Done?
The questions are: a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'. b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant? c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated.
I am having great difficulty with cascading deletes, delete triggers and referential integrity.
The database is in First Normal Form.
I have some tables that are child tables with two foreign keyes to two different parent tables, for example:
Table A / Table B Table C / Table D
So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.
SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.
Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.
When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?????
This is an example of my delete trigger:
CREATE TRIGGER [DeleteA] ON A FOR DELETE AS Delete from B where MeetingID = ID; Delete from C where MeetingID = ID;
And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.
So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.
So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).
I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.
Tables are:
Brokers and it's PK is BID
The 2nd table is Broker_Rates which also has a BID table.
I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.
I know this isn't correct syntax but should hopefully clear up what I'm asking
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
I'm using SqlDataSource and an Access database. Let's say I got two tables:user: userID, usernamemessage: userID, messagetextLet's say a user can register on my website, and leave several messages there. I have an admin page where I can select a user and delete all of his messages just by clicking one button.What would be the best (and easiest) way to make this?Here's my suggestion:I have made a "delete query" (with userID as parameter) in MS Access. It deletes all messages of a user when I type in the userID and click ok.Would it be possible to do this on my ASP.net page? If yes, what would the script look like?(yes, it is a newbie question)
The requirement is: I should allow single row delete from a table but not bulk delete. An audit table should get updated if there is any single delete or single update. So I wrote the triggers as follows: for single and bulk delete
ALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance] ON [dbo].[tbl_attendance] AFTER DELETE AS
[code]...
When I try to run the website, the database error I am getting is:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:
DELETE FROM sql2005.production.dbo.products WHERE vendor='Foo' AND productId NOT IN ( SELECT productId FROM sql2000.staging.dbo.fooProductList )
The status message (and @@ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate.
I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble.
Does anyone have suggestions on what might cause this sort of behavior?
I have a problem with one report on my server. A user has requested that I exclude him from receiving a timed email subscription to several reports. I was able to amend all the subscriptions except one. When I try to remove his email address from the subscription I receive this error:
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
For more information about this error navigate to the report server on the local server machine, or enable remote errors
Online no help couldn't offer any advice at all, so I thought I'd just delete the subscription and recreate it again, but I receive the same message. "Okay, no problem, I'll just delete the report and redeploy it and set up the subscription so all the other users aren't affected", says I. "Oh, no!", says the report server, and then it give me this message:
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Only members of sysadmin role are allowed to update or delete jobs owned by a different login. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DBInterface.DeleteObject(String objectName) at Microsoft.ReportingServices.Library.RSService._DeleteItem(String item) at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch() --- End of inner exception stack trace ---
What's even weirder is that I'm the owner and creator of the report and I'm a system admin and content manager on the report server and I set up the subscription when the report was initially deployed. Surely I should have sufficient rights to fart around with this subscription/report as I see fit?
I have rebooted the server, redeployed the report, checked credentials on the data source and tried amending and deleting from both the report manager and management studio but still I am prevented from doing so.
This one is weird and I am missing something fundamental on this one. A developer was getting a timeout with this...
CREATE PROCEDURE p_CM_DeleteBatch ( @SubmitterTranID VARCHAR(50) ) AS DECLARE @COUNT INT, @COMMIT INT
SET @COUNT = 0 SET @COMMIT = 1 --DO NOT CHANGE THIS. The Operation will be commited only when this value is 1
select @COUNT = COUNT(*) from claimsreceived where (claimstatus NOT IN ('Keyed', 'Imported')) AND SubmitterTranID = @SubmitterTranID
IF (@COUNT = 0) --This means that that Claims under this Batch have not been adjudicated & it is safe to delete BEGIN BEGIN TRANSACTION DELETE FROM INVOICECLAIMMAPPING WHERE CLMRECDID IN (SELECT DISTINCT CLMRECDID FROM CLAIMSRECEIVED WHERE SUBMITTERTRANID = @SUBMITTERTRANID) IF (@@ERROR <> 0) SET @COMMIT = 0
DELETE FROM ClaimsPayment WHERE SubmitterTranID = @SubmitterTranID IF (@@ERROR <> 0) SET @COMMIT = 0
DELETE FROM ClaimsPaymentServices WHERE SubmitterTranID = @SubmitterTranID IF (@@ERROR <> 0) SET @COMMIT = 0
DELETE FROM ClaimsreceivedPayorServices where ClmRecdPyID in (SELECT ClmRecdPyID FROM ClaimsReceivedPayors WHERE SubmitterTranID = @SubmitterTranID)
IF (@@ERROR <> 0) SET @COMMIT = 0
DELETE FROM ClaimsReceivedPayors WHERE ClmRecdid in (SELECT ClmRecdID FROM ClaimsReceived WHERE SubmitterTranID = @SubmitterTranID) IF (@@ERROR <> 0) SET @COMMIT = 0
DELETE FROM ClaimsReceivedServices WHERE SubmitterTranID = @SubmitterTranID IF (@@ERROR <> 0) SET @COMMIT = 0 DELETE FROM ClaimsReceived WHERE SubmitterTranID = @SubmitterTranID IF (@@ERROR <> 0) SET @COMMIT = 0 DELETE FROM BATCHLOGCLAIMS WHERE SubmitterTranID = @SubmitterTranID IF (@@ERROR <> 0) SET @COMMIT = 0
IF (@COMMIT = 1) BEGIN --ROLLBACK TRANSACTION --For Testing Purpose ONLY COMMIT TRANSACTION RETURN (0) END ELSE BEGIN ROLLBACK TRANSACTION RETURN (-1) END END ELSE BEGIN RaisError ('This Batch cannot be deleted. It has claim(s) which has been Adjudicated', 16, 1) END GO
I applied a couple of indices and got ride of the uncorrelated subqueries
CREATE PROCEDURE p_CM_DeleteBatch ( @SubmitterTranID VARCHAR(50) ) AS DECLARE @COUNT INT, @COMMIT INT
SET @COUNT = 0 SET @COMMIT = 1 --DO NOT CHANGE THIS. The Operation will be commited only when this value is 1
select @COUNT = COUNT(*) from claimsreceived where (claimstatus NOT IN ('Keyed', 'Imported')) AND SubmitterTranID = @SubmitterTranID
IF (@COUNT = 0) --This means that that Claims under this Batch have not been adjudicated & it is safe to delete BEGIN BEGIN TRANSACTION
DELETE INVOICECLAIMMAPPING FROM INVOICECLAIMMAPPING JOIN CLAIMSRECEIVED ON INVOICECLAIMMAPPING.CLMRECDID = CLAIMSRECEIVED.CLMRECDID WHERE CLAIMSRECEIVED.SUBMITTERTRANID = @SUBMITTERTRANID
IF (@@ERROR <> 0) SET @COMMIT = 0
DELETE FROM ClaimsPayment WHERE SubmitterTranID = @SubmitterTranID IF (@@ERROR <> 0) SET @COMMIT = 0
DELETE FROM ClaimsPaymentServices WHERE SubmitterTranID = @SubmitterTranID IF (@@ERROR <> 0) SET @COMMIT = 0
DELETE ClaimsreceivedPayorServices FROM ClaimsreceivedPayorServices JOIN ClaimsReceivedPayors ON ClaimsreceivedPayorServices.ClmRecdPyID = ClaimsReceivedPayors.ClmRecPyID WHERE ClaimsReceivedPayors.SubmitterTranID = @SubmitterTranID IF (@@ERROR <> 0) SET @COMMIT = 0
DELETE ClaimsReceivedPayors FROM ClaimsReceivedPayors JOIN ClaimsReceived ON ClaimsReceivedPayors.ClmRecdid = ClaimsReceived.ClmRecdid WHERE ClaimsReceived.SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0) SET @COMMIT = 0
DELETE FROM ClaimsReceivedServices WHERE SubmitterTranID = @SubmitterTranID IF (@@ERROR <> 0) SET @COMMIT = 0
DELETE FROM ClaimsReceived WHERE SubmitterTranID = @SubmitterTranID IF (@@ERROR <> 0) SET @COMMIT = 0
DELETE FROM BATCHLOGCLAIMS WHERE SubmitterTranID = @SubmitterTranID IF (@@ERROR <> 0) SET @COMMIT = 0
IF (@COMMIT = 1) BEGIN --ROLLBACK TRANSACTION --For Testing Purpose ONLY COMMIT TRANSACTION RETURN (0) END ELSE BEGIN ROLLBACK TRANSACTION RETURN (-1) END END ELSE BEGIN RaisError ('This Batch cannot be deleted. It has claim(s) which has been Adjudicated', 16, 1) END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON
GO
Suddenly this constraint was being violated with the change
Is the delete on ClaimsReceivedPayors starting before the delete on ClaimsreceivedPayorServices finishes? If so why would it matter between the join and subquery? This one is making me depressed because I can not explain it.
Need some advice solving a little problem I have with my database!
Current setup:
I have a person table that is made up of 39 columns. I also allow for person records to be deleted but I do this by having another table I call LogicallyDeletedrecords. This table is made up of the PersonId, Reason for deletion/suppression and a date time stamp. To access Live records I created a view based on my Person table which contains a WHERE clause to exclude records that exist in the LogicallyDeletedrecords. Similarly, I have another view DeadPersonData which contains Person records that have been removed. Hope it all makes sense so far! Now on to my worries!
The problem:
My Person table contains 9+ million records. The LogicallyDeletedrecords table has 500k+ but I anticipate further growth over the coming weeks/months. My worry is that my LivePersonData view will be too slow to access as my LogicallyDeletedrecords table grows. What’s more, as part of my Load routine, I have to make sure that Person data loaded on to the system is excluded if that same person exists as a deleted member. Both of these actions could slow down my system as the deleted table grows.
My thoughts:
I’ve been thinking of physically deleting dead Person records from my person table (possibly creating an archive table to hold them). But then if I delete them how do I cross check the details when new Person details get loaded?! As I said, my current LogicallyDeletedrecords table holds the PersonId, ReasonDeleted and CreationStamp. The only way is to add further columns which I use to match Person Details?
there are two tables involve in replication let say table1 and replicated table is also rep.table1.
we are not deleting records physically in table1 so only a bit in table1 has true when u want to delete a record but the strange thing is that replication agaent report that this is hard delete operation on table1 so download and report hard delete operation and delete the record in replicated table which is very crucial.
plz let me know where am i wrong and how i put it into right way.
there is no triggers on published tables and noother trigger is created on published table.
Is it possible to achieve this using triggers:When someone tries to delete a row in table A, the trigger should first delete a corresponding row in table B and then delete the row in table A. The reason being that, there is a foreign key set on Table B that references table A. So any attempt to delete a row in table A without deleting the corresponding row from B, throws an error.
Hello All, I have to write Trigger for Update, I have two tables, one is for storing records of current values, and one is for storing history of values. How to Write a Trigger on Main Table. As we have Inserted and Deleted Tables through which we can find Values, We dont have any Table for UPDATED Values. Help me. General Problem
I need to create a set of rows every time a new row is inserted into a table. Example (I think this would work)... select @insertedId = column1 from insertedselect @id = column1 from table1 where column2 in (select column1 from table2 where column2 = @insertedId)insert into table3 values(x, y, @id) Is it possible to do the same kind of thing in a situation where the select statement returns multiple values and execute the insert statement for each of these values? Also, if table3 was in fact the table on which the trigger acts, would it then be executed for every row created by the trigger? Sorry if I sound confused. I am.
hi everybody..i tried to put thios loop in sql server 2000 But it is not taking The @ action taken value ,,it is only taking the default value of @actiontaken value. SET @ActionTaken = 'A' IF (@AType = 'A')IF @Status= 'O' IF (@KAppInd ='Y' AND @DAppInd=null)BEGINSET @ActionTaken = 'O'END
Please tell me other option in sql server 2000 for setting variable value based on conditions
Hi using triggers i try to insert some values in to my 2 tables: But its showing teh error as "The request for procedure 'Triginsert123s' failed because 'Triginsert123s' is a trigger object." This is my code in back end: sqlcon.Open() Dim cmd As New SqlCommand("Triginsert123s '" & txtID.Text & "','" & txtName.Text & "','" & txtRole.Text & "','" & txtDep.Text & "'", sqlcon) cmd.ExecuteNonQuery() sqlcon.Close() My trigger is: CREATE TRIGGER Triginsert123s ON [dbo].[EmpRole] FOR INSERT AS declare @Eid as tinyint, @Ename as varchar(50), @Role as char(10) Insert into Emprole(Eid,Ename,Role) values(@Eid,@Ename,@Role) insert into empdep(eid,dep) values(@eid,@Role) Whats the probs?, Plz i am new to triggers help me,
Hi All, I'm using triggers to handle my transaction log to cature inserts and updates. It works fine except if the user clicks on the Save button more than once, the trigger is fired and the record is written to the log even if the record wasn't changed. Does anyone know how to check if the record was actually changed so that it isn't written to the table if it wasn't?
When I execute a stored proc from my asp.net page, will the results of a trigger be returned to my program?
For instance say my stored proc is:
Update Employees set (Lastname = @Lastname) where ID = @ID
And my trigger is:
CREATE TRIGGER tr_Employees_U on Employees FOR UPDATE AS IF UPDATE(lastname) BEGIN RAISERROR ('cannot change lastname', 16, 1) ROLLBACK TRAN RETURN END GO
It seems like since this is an AFTER trigger that my webpage would actually get a valid return code from my stored procedure however the trigger would rollback those changes correct? Or would the trigger get fired and send it's return code to my webpage?
I'm trying to write an instead of trigger for a view in SqlExpress...the table and views are defined as such:CREATE TABLE [dbo].[Work]( [WorkID] [int] IDENTITY(1,1) Primary Key, [ResourceID] [int] NOT NULL, [TaskID] [int] NOT NULL, [WorkDate] [datetime] NOT NULL, [WorkQuantity] [float] NOT NULL, [IsEstimate] [bit] NOT NULL DEFAULT ((0)), [Project] [int] NOT NULL,);CREATE VIEW [dbo].[ActualWork]ASSELECT WorkID, ResourceID, TaskID, WorkDate, WorkQuantity, ProjectFROM dbo.[Work]WHERE (IsEstimate = 0);CREATE VIEW [dbo].[EstimatedWork]ASSELECT WorkID, ResourceID, TaskID, WorkDate, WorkQuantity, ProjectFROM dbo.[Work]WHERE (IsEstimate = 1);Given that, what is wrong with the following create trigger statement:Create Trigger trg_InsertActualWork ondbo.ActualWork Instead of InsertasBEGIN Insert into dbo.Work( ResourceID, TaskID, Project, WorkDate, WorkQuantity, IsEstimate ) values ( inserted.ResourceID, inserted.TaskID, inserted.Project, inserted.WorkDate, inserted.WorkQuantity, 0 );END
l'm trying to build a trigger on a table. The reason for the trigger is to check a certain field for the first three characters if it has ie abc it must update another field in this case loanbook to newabc.How do l write the trigger so that it also check if exists and perform the updates. Please help its Urgent. l've listed the trigger below.
CREATE TRIGGER UpdTest_TRGData ON Test_TRG FOR insert,Update AS
IF left('Loan_No',3)='ABC' update Test_TRG set loanbook = 'NEWABC'
else
IF left('Loan_No',3)='DEF' update Test_TRG set loanbook = 'NEWDEF' where loanbook is null
else
update Test_TRG set loanbook =left('Loan_No',3) where loanbook is null
I'm a bit confused on this bit please elaborate : " FROM Test_TRG t INNER JOIN inserted i ON t.PK = i.PK ". The PK is on which field? Basically this trigger should ensure that on insertion of o new loan if The left(loan_no,3)=MCG and its null then NEWMCG left(loan_no,3)=MCG and its null then NEWMCG left(loan_no,3)=KVS and its null then NEWKVS left(loan_no,3)=MFS and its null then MFS left(loan_no,3)=TCR and its null then TCR left(loan_no,3)=ABL and its null then ABL
Listed below is what l've tried to do but l'm missing the PK part.Otherwise everything else you explained in the script is clear. Thanks man its urgent. When l parse the query its fine , but When l run it l get an error.
CREATE TRIGGER UpdTest_TRGData ON Test_TRG FOR INSERT, UPDATE AS
UPDATE Test_TRG SET LoanBook = CASE WHEN LEFT( i.Loan_No, 3 ) = 'MCG' THEN 'NewMCG' WHEN LEFT( i.Loan_No, 3 ) = 'KVS' AND i.LoanBook IS NULL THEN 'NewKVS' WHEN LEFT( i.Loan_No, 3 ) = 'MFS' AND i.LoanBook IS NULL THEN 'MFS' WHEN LEFT( i.Loan_No, 3 ) = 'ABL' AND i.LoanBook IS NULL THEN 'ABL'
WHEN i.LoanBook IS NULL THEN LEFT( i.Loan_No, 3 ) END FROM Test_TRG t INNER JOIN inserted i ON t.PK = i.PK -- Fill in Primary Key or other Join Column(s) WHERE LEFT( i.Loan_No, 3 ) = 'MCG' OR ( LEFT( i.Loan_No, 3 ) = 'KVS' AND i.LoanBook IS NULL ) OR i.LoanBook IS NULL
====== Error message =================
Server: Msg 207, Level 16, State 3, Procedure UpdTest_TRGData, Line 11 Invalid column name 'PK'. Server: Msg 207, Level 16, State 1, Procedure UpdTest_TRGData, Line 11 Invalid column name 'PK'.
I an loading records from a flat file into a table, which is done everyday by a scheduled job in SQL Server 7.0.
How can I make sure that if the job is run twice in a day for some reason that the same rows are not inserted into the table again? Do I have to write a insert trigger on the table ??? If so how can I achive the objective ??
