How to create DML Trigger in SQL server?

Neeks
Posted by Neeks under Sql Server category on | Views : 16111
--This is Just an example to Insert the details related to 
--inserted record using the Trigger.
--In this example we are inserting the details in the tbl_student
--table. But, the entry related to the Inserted record is reflected into
--tbl_test teble.
create Trigger test_trigger
on tbl_student
AFTER Insert --{Different options for creating the Trigger [FOR, AFTER, INSTEAD OF]}
as
begin
declare @varTest int;
select @varTest = intID from inserted
--Value you insert in the Table tbl_student is stored in Teble inserted
insert into tbl_test (test) values (@varTest) --Here, we are inserting the details in other table (i.e. tbl_test)
end

Comments or Responses

Posted by: Muhsinathk on: 9/13/2012 Level:Bronze | Status: [Member] | Points: 10
We can create a trigger to update the 'product_price_history' table when the price of the product is updated in the 'product' table.

1) Create the 'product' table and 'product_price_history' table

CREATE TABLE product_price_history

(product_id number(5),

product_name varchar2(32),

supplier_name varchar2(32),

unit_price number(7,2) );


CREATE TABLE product

(product_id number(5),

product_name varchar2(32),

supplier_name varchar2(32),

unit_price number(7,2) );

2) Create the price_history_trigger and execute it.

CREATE or REPLACE TRIGGER price_history_trigger

BEFORE UPDATE OF unit_price

ON product

FOR EACH ROW

BEGIN

INSERT INTO product_price_history

VALUES

(:old.product_id,

:old.product_name,

:old.supplier_name,

:old.unit_price);

END;

Login to post response