My code below creates a trigger that fires whenever a change occurs to a 'myTable' row, but this is not what I want. I only want to log changes made to a single field called 'Charges', when that field is changed I want to log it, can anyone tell me how to modify my code to do this, thanks
Create Trigger dbo.myTrigger
ON dbo.[myTable]
FOR UPDATE
AS
Declare @now DATETIME
Set @now = getdate()
BEGIN TRY
Insert INTO dbo.myAuditTable
(RowImage,Charges,ChangeDate,ChangeUser)
SELECT 'BEFORE',Charges,@now, suser_sname()
FROM DELETED
Insert INTO dbo.myAuditTable
(RowImage,Charges,ChangeDate,ChangeUser)
SELECT 'AFTER',Charges,@now, suser_sname()
FROM INSERTED
END TRY
hi,i am a beginner to ms sql server2000i have a tablecreate table ddd (a int, b int)by table structure is a bnow when i enter a value in b column suppose '2' in column bbext time when i insert a value in the column a i have to get the valuein b as 3 is thi spossible with triggersinsert into gdg values (1,2)a b1 2insert into gdg (a) values(2)a b2 3----------------> i have to get this 3 automaticallyis there any method to get thispls help mesatish
So, I know, after searching these forums, that it is possible to disable a trigger before updating a table and then enabling it again afterwords, for instance, from a stored procedure.
I might be dealing with hypotheticals here, but when I do a ...
ALTER TABLE table { ENABLE | DISABLE } TRIGGER { ALL | trigger_name [ ,...n ] }
... from a stored procedure, will it not be database wide? Should I worry about another change to the table happening in the timespan in which the trigger is disabled (which it would be for during a single update), which the trigger should have caught?
I have a requirement that if in a table update happened based on 1st condition then it should insert in one way and if update happened on second condition the insert statement will differ. That is it should insert the deleted records i.e., previous records existed in a table.The syntax is like
CREATE TRIGGER [dbo].[tr_a] on [dbo].[A] AFTER UPDATE AS BEGIN TRY IF NOT EXISTS
Now I want to create a trigger that prevents a delete on a single row (randomly chosen) from the table Workspaces. At the moment I have the following trigger, but this trigger still allows removal of single rows.
Current trigger:
CREATE TRIGGER deleteWorkspace ON Workspaces FOR DELETE AS
BEGIN DECLARE @Count int SET @Count = @@ROWCOUNT;
[Code] ....
Desired result: I want to be able to prevent a delete on a single row on the table above.
I have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field.
Hi, I'm having some difficulty creating the SQL Statement for getting some data from a table: I have the following table of data __User___Votes___Month __A_______14______2__A_______12______3__A_______17______4__A_______11______5 __B_______19______2__B_______12______3__B_______15______4
I want to beable to pull out the total number of votes a user has had over a period of months. eg Total up each users users votes for months 4 and 5 that would give: __User____TotalVotes ___A________28___B________15 An added complecation is that user B does not have any data for month 5 Any help or pointers would be fanstatic Many thanks
I have 2 views which contain the following fields: EVENT, WEEK, SUBSCRIPTION, QTY, GROSS_AMOUNT, SEASON
The 2 views differ by SEASON. I'm attempting to combine the 2 views in to a single view or table grouping by EVENT, WEEK and SUBSCRIPTION:
EVENT, WEEK, SUBSCRIPTION, Q6 (qty of season 1), A6 (gross_amount of season 1), Q7 (qty of season 2), A7 (gross_amount of season 2)
Below is my select command:
------
SELECT TOP 100 PERCENT dbo.vw_SEASON06.EVENT, SUM(dbo.vw_SEASON06.QTY) AS Q6, SUM(dbo.vw_SEASON06.GROSS_AMOUNT) AS A6, SUM(dbo.vw_SEASON07.QTY) AS Q7, SUM(dbo.vw_SEASON07.GROSS_AMOUNT) AS A7, dbo.vw_SEASON06.WEEK, dbo.vw_SEASON06.SUBSCRIPTION
FROM dbo.vw_SEASON06 FULL OUTER JOIN dbo.vw_SEASON07 ON dbo.vw_SEASON06.WEEK = dbo.vw_SEASON07.WEEK AND dbo.vw_SEASON06.SUBSCRIPTION= dbo.vw_SEASON07.SUBSCRIPTION AND dbo.vw_SEASON06.EVENT = dbo.vw_SEASON07.EVENT
GROUP BY dbo.vw_SEASON06.EVENT, dbo.vw_SEASON06.WEEK, dbo.vw_SEASON06.SUBSCRIPTION
ORDER BY dbo.vw_SEASON06.EVENT
-----
This creates the view but there are some issues. If an 'EVENT' exists in dbo.vw_SEASON07.EVENT and doesn't exist in dbo.vw_SEASON06.EVENT the value of the field 'EVENT' is set to NULL because the 'EVENT' name is returned from dbo.vw_SEASON06.EVENT. The same issue exists for 'SUBSCRIPTIONS' and 'WEEK'.
How can I create a single view/table that will include all the data from these 2 views without the NULL values in EVENT or SUBSCRIPTION?
I'm working on a social network where I store my friend GUIDs in a table with the following structure:user1_guid user2_guidI am trying to write a query to return a single list of all a users' friends in a single column. Depending on who initiates the friendship, a users' guid value can be in either of the two columns. Here is the crazy sql I have come up with to give what I want, but I'm sure there's a better way... Any ideas?SELECT DISTINCT UserIdFROM espace_ProfilePropertyWHERE (UserId IN (SELECT CAST(REPLACE(CAST(user1_guid AS VarChar(36)) + CAST(user2_guid AS VarChar(36)), @userGuid, '') AS uniqueidentifier) AS UserId FROM espace_UserConnection WHERE (user1_guid = @userGuid) OR (user2_guid = @userGuid))) AND (UserId IN (SELECT UserId FROM espace_ProfileProperty))
Hi,Would like to know the performance differenece between Multi-columnIndex vs Single Column Indexes. Let's say I have a table with col1,col2, col3 along with a primary key column and non-indexed columns.In queries, I will use col1, col2, and col3 together and some timesjust one or two of these three columns. My questions is, should Icreate one index contains col1, col2, and col3, or create 3 seperatedcolumns. I.e. each column has its own index. Any performancedifference?Thanks a lot.
1 . Can write more than one trigger for a single table(sql server 2000)?
2. how to create the editable gridview? While clicking particular cell it should be changed to Edit mode , and I want option for creating new row and delete option, search option
I want to create a trigger in SQL 2000 Enterprise, but not sure about triggers, how they work, etc. I just know that I was told I could create a trigger when new info is added to one table and check another table to see if this info is already in the other table, if not, copy the data to the other table too.
Would this be what a trigger can do for me?
what would be the best place to learn how triggers work, how to write one and install it, test it, etc?
I have 3 tables on my db, Projects, ProljectAllocationLog and Users
Project consists of Projectid(PK), ProjectName, UserID ProjectAllocationLog consists of ProjectAllocationID(PK), Projectid, UserID,Date Users consists of UserID (PK), Fullname
Over the course of time the user allocated to a project may change. The db has been set up to always show the most current user in the UserID of the Projects table,
I want to create a log that will record everytime that user has changed. (ProjectAllocationLog)
Having read through some examples posted on Forums, I believe that I can do this with a trigger, but I am not sure if I am doing it right, the trigger I have written is
Create Trigger tr_UpdateAllocationLog ON Projects AFTER Update AS If NOT UPDATE (Userid)
I do have one table "ABC" in DB "Master" & other table "XYZ" in DB "Test".
Both tables are having same structures & same data currently. Now i want to create trigger in such a way that after every insertion & updation on table "ABC" in DB "Master" will insert & update records in table "XYZ" in DB "Test" respectively.
I'm struggling with this. I'd like to perform a joined query from two or more tables and take the same field from several rows of one table into seperate fields of a single row in a new table.
How should I write the query which can return records with unique email address with any name attached?
The expected record set will be
name email --------- --------- <any> abc@abc.com <any> 123@123.com
I was thinking to select the distinct of email and just stuck at here. I encountered this problem few times and still cannot solve it. Any help will great appreciated. Thanks in advance.
I have a table with many fields but there is a single field that I do not want duplicates. If I index this specific field preventing duplicates, the entire record does not append. (The field in question is not keyed).
Hi I have aproble with stored procedure.I want to take the Data from a table with multiple rows,In the same select statement for the others select statemet.My store Proc is like this..
CREATE procedure spr_Load_TR_AccidentReport_Edit_VOwner ( @Crime_No varchar(20), @Unit_ID int ) as begin DECLARE @AD_Driver int,@AC_Cas int,@AV_Owner int,@A_Witness int DECLARE @Defect_ID varchar(100)
select @AV_Owner=Vehicle_Owner from TBL_TR_ACCIDENT_VEHICLE where Crime_No =@Crime_No and Unit_ID = @Unit_ID
(select TBL_TR_Person_OutsideDetails.OutSide_state from TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_state],
(select TBL_TR_Person_OutsideDetails.OutSide_City from TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_City]
---here I faced the problem- /*For the above Select only return one rows.But this select willreturn multiple row .I wnat to put that multiple data into a single field with comma*/
(SELECT @Defect_ID = COALESCE(@Defect_ID + ',','') + CAST(TBL_TR_VEHICLE_DEFECT.Defect_ID AS varchar(5)) FROM TBL_TR_VEHICLE_DEFECT,TBL_TR_ACCIDENT_VEHICLE WHERE TBL_TR_VEHICLE_DEFECT.Registration_Number =TBL_TR_ACCIDENT_VEHICLE.Registration_Number) select @Defect_ID
FROM tbl_TR_Accident_report,TBL_TR_Person_Details,TBL_TR_ACCIDENT_VEHICLE where tbl_TR_Accident_report.Crime_No=@Crime_No and tbl_TR_Accident_report.Unit_ID=@Unit_ID AND TBL_TR_ACCIDENT_VEHICLE.Crime_No=@Crime_No AND TBL_TR_Person_Details.Person_ID = TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner
I want to query TABLE1, row 1 so that I pull back the Names for the values stored in the ID and Other_ID fields so that my results are like: John Bob Number1
The only way around it now is that I store Other_Name in Table1.
I need help writing an Update trigger that puts the current date in a changed record. I understand the basic idea but can't seem to get it to work. Any help would be greatly appreciated
i am trying to create a trigger which when I insert a "y" on a student table in insCheck column, instructor table will create a record of the same person.
The reason is there are two tables are in my DB, student and instructor table. Student can be a instructor so whenever insCheck conlum in studnent table is checked as "y", instructor table should be populated. Both table studentId and instructorId inserted as manually so that I am not sure how i should populate the instructor's table just fName, mI, and lName and I have go back to the instructor table and populate the rest of the table manually or is there any way to poppulate the insturctorid also while trigger is populate the name onthe instructor table.
My Two tables DDL are like down below
create table student( studentId char(4) not null primary key, fName varchar(25) not null, mI char(1) null, lName varchar(25) not null, insCheck char(1) not null, );
create table instructor( instructorId char(4) not null primary key, fName varchar(25) not null, mI char(1) null, lName varchar(25) not null, instructorQual varchar(100) not null, );
CREATE TABLE Order_Details (OrderID int NOT NULL , ProductID int NOT NULL , UnitPrice money NOT NULL , Quantity smallint NOT NULL , Discount real NOT NULL , constraint Order_Details_PK Primary key (OrderID, ProductID) )
Create a Transact SQL procedure, customer_activity, that would, for a given CustomerID, return the number of orders that customer has made, average amount of all the customer orders, and the maximum customer’s order. The CustomerID should be the stored procedure’s input parameter. The stored procedure should use the view customer_orders.
what kind of code to create this? it says i need to use a view customer_orders, which i made: create view customer_orders as select orders.orderID, customerID, sum(order_details.orderID) as orderamount from orders, order_details where orders.orderID=order_details.orderID group by orders.orderID, customerID
but i don't know how to do it.
Next: the trigger: Write a trigger that would, for any order entered (inserted), print the order amount as well as the customer’s average and maximum order so far, by using the view and the stored procedures created in this lab.
I have problem with Triggers,Iv never done it before except @school!!
One of our clients Server has same database names(WeighBridge) but Different Instances(1got Express and Other3 have SQL2005).There is a weighbridge scale on SQL Express Database. I want to create a Trigger that Automatically updates everytime there is weighbridge scale In other Databases that have SQL2005. If someone can help please a code or tell me what to do, Create a Trigger on a Table ot Database?? Please Help!!!!!!
I have a database which once every week gets backup/restored and then renamed for training purpose. The problem is that I got trigger on some of the tables and once the database gets renamed the trigger failed to work as it referring to the old database name. I have idenify all the trigger and done a ALTER TRIGGER. However I can't seem to get it to run as a JOB. Tried to create a store procedure but get a error as the ALTER TIGGER is after CREATE PROCEDURE I could do it as a SQLstring but theirs alot of trigger, want to know if there any other ways of doing it. Please help
