 |
|
| Computers Forum Index » Computer - Databases - MS SQL Server » Creating a INSTEAD OF trigger... |
|
Page 1 of 1 |
|
| Author |
Message |
| Jed Fletcher... |
 Posted: Thu Oct 22, 2009 10:08 am |
|
|
|
Guest
|
I have a update statement
UPDATE Stock SET Qty = " + Qty + " WHERE Code = '" + dr.Cells
["codeDataGridViewTextBoxColumn"].Value.ToString() + "'
I want this trigger to retrieve the parameters from this update
statement and put them into another table.In short I want to update
the stock table and if the updated Qty is not equal to the original
Qty i want to insert the Qty into the variance table.
CREATE TRIGGER VarianceReport
ON Stock
INSTEAD OF UPDATE
AS
IF (Stock.Qty <> u.Qty)
BEGIN
SET NOCOUNT ON
INSERT INTO Variance(Qty)
SELECT u.Qty
FROM updated u
END |
|
|
| Back to top |
|
|
|
| Erland Sommarskog... |
| Posted: Thu Oct 22, 2009 9:23 pm |
|
|
|
Guest
|
Jed Fletcher (jedfletcher1 at (no spam) gmail.com) writes:
Quote: I have a update statement
UPDATE Stock SET Qty = " + Qty + " WHERE Code = '" + dr.Cells
["codeDataGridViewTextBoxColumn"].Value.ToString() + "'
No, no.
cmd.CommantText = "UPDATE dbo.Stock Set Qty = at (no spam) qty WHERE Code = at (no spam) code";
cmd.Parameters.Add(" at (no spam) qty", SqlDbType.Int);
cmd.Parameters.Value[" at (no spam) qty] = Qty;
cmd.Parameters.Add(" at (no spam) code", SqlDbType.VarChar, 4000);
cmd.Parameters.Value[" at (no spam) code"] =
dr.Cells["codeDataGridViewTextBoxColumn"].Value;
You should always use parameterised statments. You avoid risk for SQL
injection, and you utilise the SQL Server cache better. And the SQL
code is easier to read.
Quote: I want this trigger to retrieve the parameters from this update
statement and put them into another table.In short I want to update
the stock table and if the updated Qty is not equal to the original
Qty i want to insert the Qty into the variance table.
CREATE TRIGGER VarianceReport
ON Stock
INSTEAD OF UPDATE
AS
IF (Stock.Qty <> u.Qty)
BEGIN
SET NOCOUNT ON
INSERT INTO Variance(Qty)
SELECT u.Qty
FROM updated u
END
If I understand this correctly, you want
CREATE TRIGGER VarianceReport ON Stock AFTER UPDATE
INSERT Variance(Qty)
SELECT d.Qty
FROM inserted i
JOIN deleted d ON i.keycol = d.keycol
WHERE i.qty <> d.qty
I changed the trigger to an AFTER trigger, because I could not see any
point with having it as an AFTER trigger.
In a trigger you have access to the virtual tables inserted and deleted
which holds the data after and before the update respectively. You also
have to keep in mind that a trigger fires once per statment, and these
table thus can include many rows.
--
Erland Sommarskog, SQL Server MVP, esquel at (no spam) sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Mon Dec 07, 2009 3:44 pm
|
|