Creating An Update Trigger
Aug 16, 2004I 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
View 1 Replies
ADVERTISEMENT
Creating An Update Trigger
May 22, 2008I 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!!!!!!
Creating Trigger With Update
May 30, 2006Hello
I am new to sql and asp, I am using visual web developer and have table that when it gets change I would like to see another table populated with the information. This is the two tables I have
First one has the information in it that users will insert in it
asset_id int Unchecked
asset_desc varchar(50) Checked
serial_no varchar(50) Unchecked
model_no varchar(50) Checked
category bigint Unchecked
Manufacturer varchar(50) Checked
Mac_address varchar(50) Checked
service_pack varchar(50) Checked
owner bigint Unchecked
location bigint Unchecked
date_acquired datetime Checked
date_deactivated datetime Checked
system_asset_no varchar(50) Checked
cs_desc varchar(50) Checked
vendor varchar(50) Checked
modified_date datetime Checked
action varchar(50) Checked
Unchecked
Next table is the one I want the information to go in
history_asset_id int Unchecked
history_asset_desc varchar(50) Checked
history_serial_no varchar(50) Checked
history_model_no varchar(50) Checked
history_category bigint Checked
history_manufacturer varchar(50) Checked
history_mac_address varchar(50) Checked
history_service_pack varchar(50) Checked
history_owner bigint Checked
history_location bigint Checked
history_date_acquired datetime Checked
history_date_deactivated datetime Checked
history_system_asset_no varchar(50) Checked
history_cs_desc varchar(50) Checked
history_vendor varchar(50) Checked
[modified date] datetime Checked
action varchar(50) Checked
Unchecked
the column action is for the name of person updating and modified date is the system date. My trigger is this
Create TRIGGER Trigger4
ON dbo.t_asset
FOR INSERT /* INSERT, UPDATE, DELETE */
AS
INSERT INTO history_asset_id
(history_asset_id, history_asset_desc, history_asset_orderno, history_asset_invoiceno, history_asset_yellowno, history_asset_serial_number,history_asset_cost, history_asset_fedpart, history_date_acquired, history_asset_cond, history_cat_id, history_bld_id, history_loc_name,history_date_deactivated, history_asset_dispvalue, action, modified_date)
VALUES ('@asset_id','@asset_desc','@asset_orderno','@asset_invoiceno','@asset_yellowno','@asset_serial_number','@asset_cost','@asset_fedpart','@date_acquired','@asset_cond','@cat_id','@bld_id','@loc_name','@date_deactivated','@asset_dispvalue','@action','@sysdate')
Can anyone please help me or point me in the right direction, rbynum@kansascommerce.com
Creating A Simple Update Trigger
Jul 20, 2005I am extremely new at SQL Server2000 and t-sql and I'm looking tocreate a simple trigger. For explanation sake, let's say I have 3columns in one table ... Col_1, Col_2 and Col_3. The data type forCol_1 and Col_2 are bit and Col_3 is char. I want to set a trigger onCol_2 to compare Col_1 to Col_2 when Col_2 is updated and if they'rethe same, set the value on Col_3 to "Completed". Can someone pleasehelp me?Thanks,Justin
View 7 Replies View RelatedCreating Update Trigger That Involve Two Tables And Few Fields?
Oct 13, 2013I need creating an update trigger that involved two tables and a few fields.
tblCases
Fields
Defendent1
Defendent2
Defendant3
tblCaseBillingDetails
Fields
DefCount
I would like to create the trigger on tblCaseBillingDetails so that when the data in the Defendant fields are updated, the trigger fires and updates the Defendant count DefCount.
Creating Trigger On Creating Table
Jan 28, 2008
Hi,
If I want to automatically insert a record which has default value in a table,
how can I create the trigger?
Trouble With Update Trigger Modifying Table Which Fired Trigger
Jul 20, 2005Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View 1 Replies View RelatedTrigger To Update One Record On Update Of All The Tables Of Database
Jan 3, 2005hi!
I have a big problem. If anyone can help.
I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.
I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.
But i don't know exactly how to do the coding for this?
Is there any other way to do this?
can DBCC help to retrieve this info?
Please advise me how to do this.
Thanks in advance.
Vaibhav
Transact SQL :: Firing After Update Trigger - On Table Row Update
Jul 8, 2015I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
Â
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),
I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.
I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?
Update Trigger Behaviour W/o A Trigger.
May 30, 2008Hi,
I am not sure if this is the right forum to post this question.
I run an update statement like "Update mytable set status='S' " on the SQL 2005 management Studio.
When I run "select * from mytable" for a few seconds all status = "S". After a few seconds all status turn to "H".
This is a behaviour when you have an update trigger for the table. But I don't see any triggers under this table.
What else would cause the database automatically change my update?
Could there be any other place I should look for an update trigger on this table?
Thanks,
Trigger To Update A Table On Insert Or Update
Feb 15, 2008
Hello
I've to write an trigger for the following action
When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz
all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated
statut_tiers to 1
and date_cloture to the same date as entered
the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture
thank you for your help
I've never done a trigger before
Creating A Trigger - Not Sure How
Dec 7, 2005I 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?
Creating A Trigger
Oct 22, 2007I 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)
DECLARE @PROJECTID
DECLARE @NEWUSER
DECLARE @PREVIOUSUSER
SET @PROJECTID= (SELECT projected FROM Inserted)
SET @NEWUSER = (SELECT UserID from Inserted)
SET @ PREVIOUSUSER = (SELECT UserID from Deleted)
If @NEWUSER <> @PREVIOUSUSER
INSERT INTO ProjectAllocationLog (ProjectID, UserID, Date) VALUES (@PROJECTID, @NEWUSER, GETDATE())
Go
I would appreciate any comments
Need Help While Creating Trigger
Apr 17, 2008Hello,
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.
Can any one help me out?
Thanks
Prashant Hirani
Where Dreams Never Ends
Creating A Insert Trigger
Jun 5, 2004i 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,
);
thanks for your help in advance!
gazawaymy
Creating A Sp Procedure And A Trigger Help
Feb 29, 2008There are 2 tables: OrderID and Order_Details
CREATE TABLE Orders
(OrderID int
Constraint Order_ID_pk primary key
, CustomerID nchar (5)
, EmployeeID int
, OrderDate datetime
, RequiredDate datetime
, ShippedDate datetime
, ShipVia int
, Freight money
, ShipName nvarchar (40)
, ShipAddress nvarchar (60)
, ShipCity nvarchar (15)
, ShipRegion nvarchar (15)
, ShipPostalCode nvarchar (10)
, ShipCountry nvarchar (15)
)
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.
please and thank you
Creating A Job To Altering Trigger
May 22, 2008Hi Everyone,
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
Creating TRIGGER Need To Autoincrement PK
Apr 6, 2014I am creating a trigger in SQL Server that will activate when my Athelete table has something "Inserted"
So far it works except for the fact that it won't insert a null into the Equipment_ID field. I need it to autoincrement, or do the last number + 1...
CREATE TRIGGER TRG_NEW_EQUIPMENT1
ON ATHLETE AFTER INSERT
AS
BEGIN
INSERT INTO Equipment Equipment_ID, Equipment_Model, Equipment_Year, Equipment_Brand, Equipment_Color, Equipment_Condition_Rating)
VALUES ('Big Spin','2016','K2','Blue','5')
END;
GO
Creating A Delete Trigger
Feb 29, 2008I've got a ContactInfo table, that stores a variety of information about a contact (i.e. first/last name, address, phone, date of birth, status, etc.). I seem to be randomly loosing contacts though. Older backup's of my database still show a given contact, but the current one doesn't. It's happened a few times, and I can't seem to track what's causing it. (Nothing that I'm doing *should* be causing the contact to be deleted).
I'm looking to create a delete trigger for the table, so that any time a record gets delete, it will record what record got deleted (contactID, firstName, lastName), as well as what time it got deleted, and if possible, what function cause the delete (what function was accessing the table when the delete happened).
I've got some basic knowledge of SQL, and SQL statements, but my knowledge is limited... so any help on this would be greatly appreciated - or, if someone could point me to a website with good suggestions and examples, to help me create a trigger to monitor this stuff. I would be greatly indebted for any help that could be offered.
Here is kind of a shell for a delete trigger that I have been able to put together from some various examples I've found.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'trDeleteContactInfo' AND type = 'TR')
DROP TRIGGER trDeleteContactInfo
GO
CREATE OR REPLACE TRIGGER trDeleteContactInfo
on ContactInfo
FOR DELETE
AS
EXEC master..xp_sendmail 'my@email.com',
'Contact has just been deleted from ContactInformation Table'
GO
Creating A Trigger With 2 Parameters
Mar 5, 2008I'm using a Database called COMPANY and witihn this I have a table called Works_on.
I'm looking to create a trigger for update on Works_on that if the Hours coloum changes it MUST NOT be reduced and also that it can't be increase by more than 5%
Any idea how I can code this??
Creating A Delete Trigger
Feb 28, 2008
I've got a ContactInfo table, that stores a variety of information about a contact (i.e. first/last name, address, phone, date of birth, status, etc.). I seem to be randomly loosing contacts though. Older backup's of my database still show a given contact, but the current one doesn't. It's happened a few times, and I can't seem to track what's causing it. (Nothing that I'm doing *should* be causing the contact to be deleted).
Can anyone help me with setting up a simple Delete Trigger, so that whenever a contact gets deleted from the table, it will log as munch information as possible about what just happened (maybe what functions just got ran, what info just changed, whatever...); so that way I can try and get some more information on the problem, and try and diagnose what is causing the deletions.
I've got some basic knowledge of SQL, and SQL statements, but my knowledge is limited... so any help on this would be greatly appreciated - or, if someone could point me to a website with good suggestions and examples, to help me create a trigger to monitor this stuff. I would be greatly indebted for any help that could be offered.
Creating A Primary Key In A Trigger
Sep 17, 2007
I'd like to create a primary key value (incremental) within a trigger and set it in a primary key column.
Any idea anyone? Do I define my trigger as a On INSERT, Instead of INSERT? I tried both but it doesn't seem I'm doing things right.
Creating A Trigger That Sends An Email
Jul 26, 2000Can anyone assist me?
I am trying to create a trigger within our local database that will allow me submit an email to a local exchange server. Its an internal email that our company has created for responses by candidates when the original stored procedure meets a condition of TRUE.
Is SQL 7.0 capable of sending emails to a specified address through a Trigger? I was told it could but have yet to come across any documentation backing this up.
Would anyone know the generic syntax I can use to create such a trigger?
Any suggestions would be appreciated.
Thanks in advance,
Claude Johnson
cjohnson@staffmentor.net
Urgent Help Needed - Creating A Trigger?
Feb 16, 2007I want a stored procedure to run everytime the batch Status field is set to 'Released'
<Code>
CREATE TRIGGER [CSITSS].[tdecker].[FB401BV_TRIGGER] ON [CSITSS].[dbo].[Fbbatch] FOR UPDATE [Status]
AS
DECLARE @RC int
DECLARE @Batch int
SET @Batch = (??? BATCH NUMBER THAT WAS SET TO RELEASE ???)
EXEC @RC = [CSITSS].[tdecker].[GLP_FB401BV_BATCH] @Batch
</Code>
Problem Creating TRIGGER On A VIEW
Feb 11, 2004Hello,
I have a problem that definitely has me stumped.
I have a view that looks at data in a different database. Some of the fields in the view are updateable and some are not. I am trying to create a trigger against the view that will allow me to audit the updates into an audit table. I am having problems when trying to execute the CREATE TRIGGER statement.
I keep getting the message...
Server: Msg 208, Level 16, State 4, Procedure updDocInfo, Line 1
Invalid object name 'vwDC_DocInfo'.
Where vwDC_DocInfo is the name of the view.
Does anyone have any idea why I might be getting this error? The VIEW definitely does exist and I am executing the script in the same database as the view.
The script is included below...
CREATE TRIGGER updDocInfo
ON [vwDC_DocInfo]
FOR UPDATE AS
DECLARE @ModifiedDate AS DATETIME
SELECT @ModifiedDate = GETDATE()
-- Audit OLD record.
INSERT tblAudit_DC_DocInfo
SELECT
0 AS AuditType,
ItemID,
Comment,
VersionComment,
CheckedOut,
Title,
BaseParagonDocumentNumber,
Author,
ClientDocumentNumber,
ClientDocumentType,
ClientJobNumber,
[Module],
Unit,
SequenceNumber,
RevisionDate,
ApprovedBy,
CheckedDepartmentManager,
CheckedLeadEngineerDesigner,
IssueType,
RevisedByDesigner,
RevisedByEngineer,
RevisionCode,
HSECheck,
CurrentVersionNumber,
CurrentVersionDate,
USER AS ChangedByUser,
@ModifiedDate AS DateChanged
FROM DELETED DEL
-- Audit NEW record.
INSERT tblAudit_DC_DocInfo
SELECT
0 AS AuditType,
ItemID,
Comment,
VersionComment,
CheckedOut,
Title,
BaseParagonDocumentNumber,
Author,
ClientDocumentNumber,
ClientDocumentType,
ClientJobNumber,
[Module],
Unit,
SequenceNumber,
RevisionDate,
ApprovedBy,
CheckedDepartmentManager,
CheckedLeadEngineerDesigner,
IssueType,
RevisedByDesigner,
RevisedByEngineer,
RevisionCode,
HSECheck,
CurrentVersionNumber,
CurrentVersionDate,
USER AS ChangedByUser,
@ModifiedDate AS DateChanged
FROM INSERTED INS
Can I Increment The Column (c1+2) ---by Creating The Trigger
Mar 10, 2006hi,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
View 1 Replies View RelatedUpdate Trigger To Update Another Table
Dec 17, 2001I have an update trigger which fires from a transactiion table to update a parent record in another table. I am getting no errors, but also no update. Any help appreciated (see script below)
create trigger tr_cmsUpdt_meds on dbo.medisp for UPDATE as
if update(pstat)
begin
update med
set REC_FLAG = 2
from deleted dt
where med.uniq_id = dt.uniq_id
and dt.pstat = 2
and dt.spec_flag = 'kop'
end
UPDATE Trigger Issue When Using UPDATE
May 30, 2008I am trying to update a fields with an UPDATE statement but I keep getting the error message when I run the query.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I have this Update trigger that I know is causing the error message because I guess it's not built to manage multi-row updates.
Can someone help me re-write it. I also tried using the WHERE p.ID = p.ID but when I do that it modifies all rows in the modifieddate column instead of just the cells/rows that I'm updating
ALTER TRIGGER [dbo].[MultitrigCA]
ON [dbo].[ProdDesc]
AFTER UPDATE
AS
SET NOCOUNT ON
IF UPDATE (codeabbreviation)
UPDATE p
sET p.ModifiedDate = GETDATE()
FROM ProdDesc AS p
WHERE p.ID = (SELECT ID FROM inserted)
Update Trigger - Update Query
Jul 20, 2005Hi there,I'm a little stuck and would like some helpI need to create an update trigger which will run an update query onanother table.However, What I need to do is update the other table with the changedrecord value from the table which has the trigger.Can someone please show me how this is done please??I can write both queries, but am unsure as to how to get the value ofthe changed record for use in my trigger???Please helpM3ckon*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedINSTEAD OF UPDATE Trigger To Hold Update
Apr 3, 2008
hi!
i have a database with about 20 tables. i appended to each table a column "UpdatedOn", and i want to write a trigger to set the date of the update date in that column, using a trigger.
i want to avoid the trigger launching for the last column (UpdatedOn).
how can i detect the rows that changed, and modify only the update date/time?
i read something about TableName_Inserted and TableName_Deleted, but i would prefer to copy as generic as possible the data from there, meaning, not to write column names in my script.
another idea i thought about was to prevent the trigger executing if no other column except for UpdatedOn changed, but... i encounter some trouble, when i try to pass column name (as string) to UPDATE() function.(Error: Expecting ID or QUOTED_ID)
thank you in advance.
Creating A Trigger To Insert Values From One Table To Another
Apr 15, 2008hello everyone ,
i have a table named "Employee" with EmpID as PK.
i want to insert EmpID one by one in another table named "AssignedComplaints"
so if i have all the EmpID inserted in "AssignedComplaints" table then on next insert operation , the first EmpID will be inserted and then second on so on.
like i gave u a sample
i have three EmpIDs in "Employee" table named M1,M2,M3
first M1 is inserted secondly M2 and lastly M3.
now i have all EmpID in "AssignedCompalints" table.
now if i do want to insert again then the whole process will repeat , first M1 will be inserted secondly M2 and so on.
i need the query
i have created a trigger and will use this query in that trigger.
thanks
Creating A Trigger To Check Before Deleting A Record
Jun 12, 2008I am using the tables created by the aspnet_regsql.exe tool for security. Basically, I need to ensure that an account named Administrator is never deleted. I also have a role named administrator, and I need to make sure that Administrator is never removed from the administrator role.Can I create a trigger to ensure that the Administrator is never deleted and that the Administrator is never removed from the Administrator role? I know it will probably be two separate triggers: one on the aspnet_users table and one on the aspnet_usersinroles table.Thanks a lot for the help!
View 1 Replies View RelatedCreating A Trigger To Automate Insert Into Another Table
Jan 14, 2008
Hi,
i am facing a problem with creating a Trigger to insert into another table.
i have 4 tables namely:
PurchaseOrder
PurchaseOrderItem
DeliveryOrder
DeliveryOrderItem
i want the trigger to create a new row in the DeliveryOrder when i creates a PurchaseOrder.
I tried the following:
CREATE TRIGGER trgInsertPO
ON PurchaseOrder
FOR INSERT
AS
INSERT INTO DeliveryOrder (DeliveryOrderNo,DeliveryOrderDate, SupplierID, DeliveryOrderStatus)
VALUES (PurchaseOrderNo,PurchaseOrderDate,SupplierID,'d')
GO
but it cant work. Help required.
Thanks.