#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    2

    How to create triggers et al


    Hi guys, i would like to create a trigger which would satisfy these requirements:


    1. Trigger on a table on a row.

    2. Trigger structure if would be after edit.

    3. the trigger will activate if someone will edit one of 4 rows of a table

    e.g

    table "A" would hold

    name age gender address salary

    if one of it is updated, the trigger would activate to update another table.

    any other kinds of solution would help. thanks you sir! i hope this makes sense but i cant incorporate this to my program because i don't have the correct source code for some reasons which is internal to us
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    117
    Rep Power
    9
    it should be something like that

    Code:
    create TRIGGER [dbo].[triggerName] ON [dbo].[TableName]
    alter INSERT,DELETE,UPDATE
    AS
    
    -- case deleted
    if not exists(select name from inserted)
    Begin
    	insert into anotherTable 
    	select * from deleted
    	/*
    	all any your code
    
    	*/
    end
    else
    begin
    	-- update
    	if update(name) or update(age) or update(gender) or update(address) or update(salary)
    	begin
    		insert into changesTable
    		select 
    		field_name = 'name'
    		new_value = i.name
    		from inserted i	
    
    		insert into changesTable
    		select 
    		field_name = 'gender'
    		new_value = i.gender
    		from inserted i	
    
    		insert into changesTable
    		select 
    		field_name = 'address'
    		new_value = i.address
    		from inserted i	
    
    		insert into changesTable
    		select 
    		field_name = 'salary'
    		new_value = i.salary
    		from inserted i	
    		
    	end
    end
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    2
    Originally Posted by gk53
    it should be something like that

    Code:
    create TRIGGER [dbo].[triggerName] ON [dbo].[TableName]
    alter INSERT,DELETE,UPDATE
    AS
    
    -- case deleted
    if not exists(select name from inserted)
    Begin
    	insert into anotherTable 
    	select * from deleted
    	/*
    	all any your code
    
    	*/
    end
    else
    begin
    	-- update
    	if update(name) or update(age) or update(gender) or update(address) or update(salary)
    	begin
    		insert into changesTable
    		select 
    		field_name = 'name'
    		new_value = i.name
    		from inserted i	
    
    		insert into changesTable
    		select 
    		field_name = 'gender'
    		new_value = i.gender
    		from inserted i	
    
    		insert into changesTable
    		select 
    		field_name = 'address'
    		new_value = i.address
    		from inserted i	
    
    		insert into changesTable
    		select 
    		field_name = 'salary'
    		new_value = i.salary
    		from inserted i	
    		
    	end
    end

    alter INSERT,DELETE,UPDATE

    --
    Sir, this portion, does this tells that the trigger should work upon insert, delete and update from the database?

    really thankful for your post!

IMN logo majestic logo threadwatch logo seochat tools logo