February 20, 2012 at 11:59 pm
Hi all
I want to write a trigger for below scenario
customer
cust_idcust_namecust_dobcust_citystate_idcust_phonecust_emailcreated_dtmodified_dt
1234Tim D2/2/1988Austin20048595098tim@gmail.com3/2/2011
1235Bill P5/3/1980New York30046748898bill_p@gmail.com4/2/2011
1278John Q6/13/1988Cheanni40084930393john@gmail.com5/5/2011
1279John Black6/13/1984Cheanni40084930356john_b@gmail.com5/6/2011
If there is no @ symbol exists in the email ID, reject that record
customer age should be > 18
when user inserted a record track the created dt as system date
When any modication happens on a customer row, update the modified date based on the system date.
any help.......
February 21, 2012 at 12:22 am
Insert Trigger
ALTER TRIGGER [Inventory].[purchase_order_detail_FillUser]
ON [Inventory].[purchase_order_detail] AFTER INSERT
AS
SET NOCOUNT ON;
Declare @TransDetail_Id Int
Select @TransDetail_Id=purchase_order_detail_id from INSERTED
IF TRIGGER_NESTLEVEL() > 1
RETURN
Update inventory.purchase_order_detail set [user_name]=(SELECT SYSTEM_USER), modify_user=(SELECT SYSTEM_USER),created_date=getdate(),modify_date=getdate() where purchase_order_detail_id=@TransDetail_Id
Update Trigger
ALTER TRIGGER [Inventory].[purchase_order_detail_FillUser_update]
ON [Inventory].[purchase_order_detail] AFTER UPDATE
AS
SET NOCOUNT ON;
Declare @TransDetail_Id Int
Select @TransDetail_Id=purchase_order_detail_id from INSERTED
IF TRIGGER_NESTLEVEL() > 1
RETURN
Update inventory.purchase_order_detail set modify_user=(SELECT SYSTEM_USER),modify_date=getdate() where purchase_order_detail_id=@TransDetail_Id
Regards
Guru
February 21, 2012 at 12:43 am
Hi,
Another way for adding created date when insert you can use default value.. Instead of passing arg/creating trigger
Regards
Guru
February 21, 2012 at 12:44 am
For email and age i think you can add constrain
February 21, 2012 at 9:32 pm
hi
Thanks to reply
February 23, 2012 at 7:46 am
GuruGPrasad (2/21/2012)
Insert TriggerALTER TRIGGER [Inventory].[purchase_order_detail_FillUser]
ON [Inventory].[purchase_order_detail] AFTER INSERT
AS
SET NOCOUNT ON;
Declare @TransDetail_Id Int
Select @TransDetail_Id=purchase_order_detail_id from INSERTED
IF TRIGGER_NESTLEVEL() > 1
RETURN
Update inventory.purchase_order_detail set [user_name]=(SELECT SYSTEM_USER), modify_user=(SELECT SYSTEM_USER),created_date=getdate(),modify_date=getdate() where purchase_order_detail_id=@TransDetail_Id
Update Trigger
ALTER TRIGGER [Inventory].[purchase_order_detail_FillUser_update]
ON [Inventory].[purchase_order_detail] AFTER UPDATE
AS
SET NOCOUNT ON;
Declare @TransDetail_Id Int
Select @TransDetail_Id=purchase_order_detail_id from INSERTED
IF TRIGGER_NESTLEVEL() > 1
RETURN
Update inventory.purchase_order_detail set modify_user=(SELECT SYSTEM_USER),modify_date=getdate() where purchase_order_detail_id=@TransDetail_Id
Regards
Guru
DO NOT DO THIS TYPE OF TRIGGER PROGRAMMING!!! This is a DEVASTATINGLY BAD DESIGN!!!
You are putting the contents of INSERTED into a variable. What happens when INSERTED has more than ONE ROW?? You LOSE DATA!! I know of a company that went out of business because they based their logic on this type of code. You MUST code triggers to do fully set-based operations if you touch DELETED or INSERTED (or use cursors on them, G-d forbid).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply