How to create simple insert trigger

I have just one table but need to create a trigger that takes place after an update on the Orders table. I need it to multiply two columns and populate the 3rd column (total cost) with the result as so:

Orders

ProductPrice ProductQuantity TotalCost
-- --
￡2.50 2
￡1.75 3
￡12.99 2

Can anyone please help me?You don't need a trigger, you need a computed column:

CREATE TABLE [dbo].[xxx](
[col_a] [int] NOT NULL default(2),
[col_b] [int] NOT NULL default(2),
[col_axb] as a * b,
) ON [PRIMARY]
GO|||Thank you for the help!

How to create an Audit Table

Can anyone help, I am able to create a trigger that will populate a audit table everytime one of my tables columns data changes, but I have an applications from another user that has a stored proceudre and when that is called from an application it hit the original table twice, so the audit table will get a duplicate entry. How do you prevent an AUDIT TABLE from inserting a duplicate entry
Here is my trigger:
Create TRIGGER tg_audit_task_order_awardees on xxx
for INSERT,UPDATE,DELETE as

INSERT INTO audit_task_order_awardees(
audit_log_type,
to_awardee,
solicitation_id,
contract_id,
order_number,
amount,
show_public,
audit_changedatetime,
audit_user)

Select
'OLD',
del.to_awardee,
del.solicitation_id,
del.contract_id,
del.order_number,
del.amount,
del.show_public,
getdate(),
del.modified_user
FROM deleted del

/* for a new record */

INSERT INTO audit_task_order_awardees(
audit_log_type,
to_awardee,
solicitation_id,
contract_id,
order_number,
amount,
show_public,
audit_changedatetime,
audit_user)
Select
'NEW',
ins.to_awardee,
ins.solicitation_id,
ins.contract_id,
ins.order_number,
ins.amount,
ins.show_public,
getdate(),
ins.modified_user
FROM inserted ins

Take a look at this article... offcourse there are other ways to do it, but this might give you some ideas to do it... I believe the only problem is it can't handle text and ntext fields:http://www.codeproject.com/database/AuditTriggers.asp

How to create a trigger to update a field

Hi -

I know my way around VS but I am just exploring "advanced" SQL Server 2005 and have run into a challenge which I think a trigger could solve, but I am not sure and also don't know how to set that up. So any help or links to tutorials are highly appreciated.

Here is the challenge: I have a table with a number of fields, among them RequestID (bigint) and Booktime (datetime). What I would like to happen is whenever someone writes a value different from NULL into RequestID, Booktime gets set to the current timestamp. When RequestID gets set to NULL, Booktime gets set to NULL.

Is there a way to do this with a trigger (or an otherwise elegant way)?

Thanks in advance for ANY help or ideas.

Oliver

Why do you need a trigger for that? Cant your application/stored proc handle the logic? Triggers are generally a maintenance nightmare (IMHO). You have to use them cautiously if you have to.

|||

Thanks for the advice. Oliver

|||

I agree with ndinakar, stay away from triggers.

Your best bet is to handle the logic in your app or stored proc. For a stored procedure you could handle it as so

Create Procedure dbo.myUpdateProc( @.fieldIdentityint, @.RequestIDbigint =NULL)If @.RequestIDISNOT NULLBegin Update <table>set RequestID = @.RequestID, Booktime =GetDate()where = @.fieldIdentityEnd

sql

How to create a trigger such that it can delete the rows whenever any other application such as

I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?

Hi,

actually Select has no trigger action, only DML operations are capable. You could use a stored procedure to read the rows rather than using a select statement (don′t know if BizTalk is able to do this through stored procedures).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks a lot Jens.

How to create a trigger such that it can delete the rows whenever any other application such as

I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?

Hi,

actually Select has no trigger action, only DML operations are capable. You could use a stored procedure to read the rows rather than using a select statement (don′t know if BizTalk is able to do this through stored procedures).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to create a CLR integrated trigger on table in schema?

In CLR integrated trigger:

If I want to make a trigger:
- For Insert
- With name: NewEmployeeInserted
- On table dbo.Employees

I add the following attribute above the desired .net method logic:
[SqlTrigger(Event = "For Insert", Name = "NewEmployeeInserted", Target = "Employees")]

How to make a trigger on for example: Production.Employees table?
where Production is the schema where this table resides.

Thank you.

Hi,

this was discussed in an earlier thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=132062&SiteID=1

Normally you should be able to only prefix the Schema before the table target.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de